NOT EXISTS using single table?

My pee-brain cannot seem to grasp what seems quite simple.
I am trying to query a single table that stores items and their warehouse
locations to find items that do not exist in a particular warehouse.
There are plenty of examples of this with 2 tables like:

This query finds the titles for which there have been no sales.
USE pubs
SELECT title
FROM titles
WHERE NOT EXISTS
(SELECT title_id
FROM sales
WHERE title_id = titles.title_id)
Given this sample data:CREATE TABLE [ItemLoc] ( [item] [varchar] (10) NOT
NULL , [loc] [char] (2) NOT NULL , )GOINSERT ItemLoc
VALUES('ABC','CA')INSERT ItemLoc VALUES('ABC','NY')INSERT ItemLoc
VALUES('ABC','FL')INSERT ItemLoc VALUES('CDE','CA')INSERT ItemLoc
VALUES('CDE','FL')INSERT ItemLoc VALUES('CDE','OH')INSERT ItemLoc
VALUES('FGH','CA')INSERT ItemLoc VALUES('FGH','OH')What query will show
items that DO NOT exist in loc 'OH'. Should only show item 'ABC'Or items
NOT IN loc 'NY'. Should show items 'CDE' and 'FGH'Thanks.

Re: NOT EXISTS using single table?

"Artie" wrote in message
news:q4slj.117$Ch6.84@newssvr11.news.prodigy.net...
> My pee-brain cannot seem to grasp what seems quite simple.
> I am trying to query a single table that stores items and their warehouse
> locations to find items that do not exist in a particular warehouse.
> There are plenty of examples of this with 2 tables like:
>
> This query finds the titles for which there have been no sales.
> USE pubs
> SELECT title
> FROM titles
> WHERE NOT EXISTS
> (SELECT title_id
> FROM sales
> WHERE title_id = titles.title_id)
> Given this sample data:CREATE TABLE [ItemLoc] ( [item] [varchar] (10) NOT
> NULL , [loc] [char] (2) NOT NULL , )GOINSERT ItemLoc
> VALUES('ABC','CA')INSERT ItemLoc VALUES('ABC','NY')INSERT ItemLoc
> VALUES('ABC','FL')INSERT ItemLoc VALUES('CDE','CA')INSERT ItemLoc
> VALUES('CDE','FL')INSERT ItemLoc VALUES('CDE','OH')INSERT ItemLoc
> VALUES('FGH','CA')INSERT ItemLoc VALUES('FGH','OH')What query will show
> items that DO NOT exist in loc 'OH'. Should only show item 'ABC'Or items
> NOT IN loc 'NY'. Should show items 'CDE' and 'FGH'Thanks.
>

SELECT item FROM ItemLoc
EXCEPT
SELECT item FROM ItemLoc WHERE loc = 'NY';

--
David Portas

Re: NOT EXISTS using single table?

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

Re: NOT EXISTS using single table?

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



"Plamen Ratchev" 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