I have two tables, table1 and table2. Table1 is in a one to many relationship with table2 (one row in table1 maps to many rows in table2). There is a field in table2 called code. If none of the values of code in table2 equal some set of values (say 1, 2, and 3), I want to select the value in table1. I'm not sure how to write this kind of join.
Assume the primary key in table1 is called id and the foreign key to which it maps is called did. Can you tell me how to write this kind of join?
This is known as antijoin.
The easiest implementation is:
SELECT * FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2
WHERE table2.did = table1.id
AND table2.code in (1,2,3))
or, using outer join (I'm not 100% sure this will work, as I always use NOT EXIST syntax myself for antijoins):
SELECT table1.*
FROM table1
LEFT OUTER JOIN
table2
ON table1.id = table2.did
AND table2.code in (1,2,3)
WHERE table2.did is NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With