Sql insert if row does not exist

Question

I have 3 tables named table1 table2 and table3 . table3 contains records that have table1.id and table2.id and some other columns. Therefore, I need to do the following. for each record in table 1, I need to see if there is a row in table3 containing this table1.id and any other table2.id, if there is no such record, I want to insert it.

so here is an example.

suppose table1

 1 ... ... ... 2 ... ... ... 

table 2

 10 .. .. .. 20 .. .. .. 

Table3

 1 | 10 .. .. .. 2 | 20 .. .. .. 

I need to add

1 20 .. .. .. and 2 10 .. .. .. rows in table3, because for table1.id 1 it did not have a row that had all table2.id (in this case 20), and for table1.id 2 also did not have a row that had all table2.id (in this case 10). any help would be appreciated

Answer 1

If I succeed, try the following:

 INSERT INTO Table3 (Table1_id,Table2_id) SELECT Tablei.id,Table2.id FROM Table1,Table2 WHERE NOT EXISTS (SELECT 1 FROM Table3 WHERE Table3.Table1_id=Table1.ID AND Table3.Table2_id=Table2.ID) 

Answer 2

Try the following:

 IF NOT EXISTS(SELECT 1 FROM Table3 WHERE Table3.Table1_ID = Table1.ID AND Table3.Table2_ID = Table2.ID) INSERT INTO Table3(Table1_ID, Table2_ID) VALUES (ID1,ID2) END IF 

Answer 3

You can also do cross join , and they insert combinations that do not exist from this cross join.

sqlFiddle

 insert into table3(col1, col2) select ta, tb from table3 right join (select table1.col as a, table2.col as b from table1 cross join table2) t on ta = table3.col1 and tb = table3.col2 where table3.col1 is null and table3.col2 is null; 

Answer 4

Another syntax:

 INSERT INTO t3 (t1id, t2id ) SELECT t1.id , t2.id FROM t1,t2 EXCEPT SELECT t1id, t2id from t3 

In addition, you can add triggers on t1 and t2 to complete the task automatically.