Posted by Erland Sommarskog on 10/23/07 21:31

Artie (artie2269@yahoo.com) writes:
> I was hoping somebody could assist me with this. I need to find
> accounts that do not contain a contact person called 'Accounting'. Each
> account may contain multiple contacts.
>
> Here's my query to find accounts that DO contain an 'Accounting' contact:
>
> SELECT company.code, company.name, company.type,
> contacts.fullname
> FROM company INNER JOIN
> contacts ON company.code = contacts.code
> WHERE (company.type in ('C', 'R')) and (contacts.fullname =
> 'Accounting')
> order by company.code
>
>
> How do I find accounts that DO NOT contain an 'Accounting' contact?

SELECT cm.code, cm.name, cm.type, ct.fullname
FROM company cm
JOIN contacts ct ON cm.code = ct.code
WHERE cm.type in ('C', 'R'))
AND NOT EXISTS (SELECT *
FROM contacts ct2
WHERE cm.code = ct2.code
AND ct2.fullname = 'Accounting')
ORDER BY cm.code



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]