PDA

View Full Version : select query for records which do not appear in another linked table

Jon GS
02-06-02, 17:51
I have a successful inner join query to select records from one table which have a corresponding record in another table with the same unique id. "select table1.name, table1.ID, table2.address from table1 inner join table2 on table1.ID=table2.ID"

How can I select records from table1 whose ID does NOT appear in table2?

Any help much appreciated.

JGS
alligatorsql.com
02-06-02, 20:19
Hello,

which database do you use ?

This would help :)

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
Jon GS
02-07-02, 14:20
used to postgesql but client now insists on MS SQL Server - alas alack!
thanks Jgs
rwilkerson
02-08-02, 22:05
First using an outer join

SELECT table1.name,
table1.ID,
table2.address
FROM table1 LEFT JOIN table2
ON table1.ID=table2.ID
WHERE table2.address IS NULL

Second using a subquery

SELECT table1.name,
table1.ID
FROM table1
WHERE NOT EXISTS ( SELECT 1
FROM table2
WHERE table1.id = table2.id )

Hope this helps.
Jon GS
02-11-02, 04:52
Really apprecate the help, used the subquery method.

Many thanks

Jon