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.

"Artie" <artie2269 (AT) yahoo (DOT) com> wrote

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

--
David Portas

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

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



"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote