Where Not Exists With One to Many Relationship

sql, sql-server, sql-server-2008-r2, t-sql

Solution

I might be over simplifying your problem, but I think this would work:

SELECT  *
FROM    ItemSale i
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    ItemSale i2
            WHERE   i.ItemID = i2.ItemID
            AND     i2.CategoryID = 1
        );

Example on SQL Fiddle

Problem

I am having a hard time figuring out how to ask this question, so I'll just go straight to the example code. Let's say I have these tables: ``` create table Item ( ItemId int identity(1,1), Name nvarchar(256) ) create table ItemSale ( ItemSaleId int identity(1,1), ItemId int, Price decimal, CategoryId tinyint ) ``` What I want to retrieve is the list of `ItemSale` records that are not in a given `CategoryId`. The complication, at least for me, is that if a record exists in `ItemSale` for a given `Item`, I do not want to see any records for that `Item`. So if I have this data: ``` insert into Item(Name) select N'Widget' union all select N'Foo' union all select N'Buzz' insert into ItemSale(ItemId, Price, CategoryId) select 1, 9.95, 1 union all select 1, 19.95, 2 union all select 3, 99.99, 3 ``` And the `CategoryId` I want to filter out is 1, then I don't want to see any records for `ItemId` 1 ("Widget"). So, with that sample data, I would only see the `ItemSale` record for `Item` with ID 3. I know that my solution will most likely involve some sort of `NOT EXISTS` OR `LEFT JOIN` but I'm struggling with how to filter out all records instead of just the specific record that matches my criteria. What am I missing? SQLFiddle: http://sqlfiddle.com/#!3/79c58

Original source