469,898 Members | 1,574 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

How do I select all records in one table that have NO related records in another table?


I can't get my head around this:

I want to select all IDs from table A that do not have a related record in
table B according to some condition:

Table A contains, say, Parents and table B contains Children. I want to
select all Parents that have no children called "Sally" (this is a noddy
example, reminds me of being at Uni again :) ).

Any ideas?
Thanks

Jul 20 '05 #1
2 2537
Try one of these:

SELECT A.id
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE B.id = A.id
AND B.name = 'Sally')

SELECT A.id
FROM A
LEFT JOIN B
ON A.id = B.id
AND B.name = 'Sally'
WHERE B.id IS NULL

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
Ahh, makes sense. Thankyou very much

"David Portas" <RE****************************@acm.org> wrote in message
news:G-********************@giganews.com...
Try one of these:

SELECT A.id
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE B.id = A.id
AND B.name = 'Sally')

SELECT A.id
FROM A
LEFT JOIN B
ON A.id = B.id
AND B.name = 'Sally'
WHERE B.id IS NULL

--
David Portas
SQL Server MVP
--

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Denis St-Michel | last post: by
3 posts views Thread by Wim Roffil | last post: by
1 post views Thread by Dave | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.