Sql insert if row does not exist

Question

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

so here is the example.

suppose table1

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

table2

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

table3

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

I need to add

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

Answer 1

If I've got it right try this:

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 this:

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