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

[Robin Tucker]

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

[David Portas]

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
--

[Robin Tucker]

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
--