Select - problem

[Roland]

Hi there!

I have two tables. Table 1 is temporary, table 2 is my
target table:

table_1 table_2
id substance id substance

101 Medicine B 101 Medicine A
102 Medicine C 101 Medicine B
104 Medicine F 102 Medicine C
103 Medicine D
103 Medicine E

Now, I wanna insert the Medicine F with ID 104 into Table 2 and Update Medicine A and B (there are much more colums in the tables which have to be gone over).

To get 101 Medicine A and B, I did a SELECT T.*
FROM table_1 AS T JOIN table_2 AS M
WHERE T.id=M.id AND
T.substance=M.substance
This works (when I replace the 'where' with 'on', I get a syntax error - I wonder why?).

How can I select 104 Medicine F?

Thanks

 

[Roland]

Got it, folks! The solution is:
SELECT t.* FROM table_1 t LEFT JOIN table_2 m ON t.id=m.id AND t.substance=m.substance WHERE m.id IS NULL AND m.substance IS NULL