Posted by Plamen Ratchev on 01/22/08 20:47

As David's solution requires SQL Server 2005, here is one for SQL Server
2000:

SELECT DISTINCT item
FROM ItemLoc AS I1
WHERE NOT EXISTS (SELECT *
FROM ItemLoc AS I2
WHERE I2.loc = 'OH'
AND I2.item = I1.item)

Since your item column does not allow NULLs, you can also write:

SELECT DISTINCT item
FROM ItemLoc
WHERE item NOT IN (SELECT item
FROM ItemLoc
WHERE loc = 'OH')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: Re: removing unused objects - SQL Server 2000 or 2005
• Prev in forum: Re: NOT EXISTS using single table?
• Thread view: Re: NOT EXISTS using single table?

[Reply to this message]