Posted by Artie on 10/24/07 00:17

Thanks Erland.


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99D2F052488E0Yazorman@127.0.0.1...
> 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]