CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Sven Grosen
Sven Grosen

Reputation: 5636

Where Not Exists With One to Many Relationship

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

Upvotes: 3

Views: 3655

Answers (1)

GarethD
GarethD

Reputation: 69789

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

Upvotes: 5

Related Questions