Posted by Artie on 01/22/08 20:09

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.

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

[Reply to this message]