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

Solution 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)

Solution 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

Solution 3

You can also make a cross join and them insert the combinations that do not exist from that cross join.

sqlFiddle

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

   where table3.col1 is null
     and table3.col2 is null;

Comments

[Jamie Marshall]

Careful - this can result in a race condition

[Raahim Fareed]

@JamieMarshall Race Condition?

[Jamie Marshall]

@RaahimFareed - if statements are notorious for their locking behavior. They're not evaluated the same as a single statement such as the accepted answer and the state of the rows being evaluated in the if statement can change before the insert actually happens. I deal with this all the time. When you use if to check records you should almost always use an explicit transaction.

[Raahim Fareed]

@JamieMarshall Makes sense, also googled on this more, thanks.