Select data from one table and insert into another existing table, which doesn't exist in the table

Question

My table scheme is as follows: (Bold column name is primary key)

Table 1: id1 - id2

Table 2: id2 - name2

Table 3: id3 - name3

Table 4: id1 - Id3

What I want to do is have sql code that :

1. Select data in id1 and id3 columns for which name2=input=name3
2. Insert into table 4
3. Only insert into 4 if id1, id3 combination doesn't exist in table 4

Currently I can do step 1 and 2, but (assuming it can be done) I cannot get the syntax for "NOT EXIST" correct for step 3.

This is my code currently:

INSERT INTO table4( id1, id3) 
SELECT id1, id3
FROM table2
INNER JOIN table1 ON table1.id2 = table2.id2
INNER JOIN table3 ON table2.name2 = table3.name3
WHERE name2 LIKE  'input'

Answer 1

Here the query you need

insert into table4(id1, id3) 
select t1.id1, t3.id3
from table2 as t2
   inner join table1 as t1 on t1.id2 = t2.id2
   inner join table3 as t2 on t2.name2 = t3.name3
where
   t2.name2 like 'input' and 
   not exists (
       select *
       from table4 as t4
       where t4.id1 = t1.id1 and t4.id3 = t3.id3
   )

as an advice - I suggest you always use aliases (and refer to column as alias.column_name) in your queries, it'll help you to avoid bugs and your queries will be more readable.