Posted by David Portas on 01/22/08 20:32

"Artie" <artie2269@yahoo.com> 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

• Next in forum: Re: NOT EXISTS using single table?
• Prev in forum: NOT EXISTS using single table?
• Thread view: Re: NOT EXISTS using single table?

[Reply to this message]