Posted by Artie on 01/22/08 23:42

I am running SQL 2000. Nice to have the 2005 script as well.
Thank you both.



"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:X9ydnarbT5AZywvanZ2dnUVZ_uWlnZ2d@speakeasy.net...
> 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: NULL values in a SELECT in another SELECT
• Prev in forum: Re: removing unused objects - SQL Server 2000 or 2005
• Thread view: Re: NOT EXISTS using single table?

[Reply to this message]