Matching all values in IN clause

Question

You can do something like this:

select ItemID
from ItemCategory
where CategoryID in (5,6,7,8) <-- de-dupe these before building IN clause
group by ItemID
having count(distinct CategoryID) = 4 <--this is the count of unique items in IN clause above

If you provide your schema and some sample data, I can provide a more relevant answer.

SQL Fiddle Example

If you want to find the items that have all of a specific set of CategoryIDs and no others, this is one way you can approach it:

select a.ItemID
from (
    select ItemID, count(distinct CategoryID) as CategoryCount
    from [dbo].[ItemCategory]
    where CategoryID in (5,6,7,8)
    group by ItemID
    having count(distinct CategoryID) = 4 
) a
inner join (
    select ItemID, count(distinct CategoryID) as CategoryCount
    from [dbo].[ItemCategory]
    group by ItemID
) b on a.ItemID = b.ItemID and a.CategoryCount = b.CategoryCount

SQL Fiddle Example

If you prefer, you could do it with a subquery:

select ItemID 
from ItemCategory 
where ItemID in (
    select ItemID 
    from ItemCategory 
    where CategoryID in (5,6,7,8) 
    group by ItemID 
    having count(distinct CategoryID) = 4
) 
group by ItemID 
having count(distinct CategoryID) = 4

SQL Fiddle Example

Leave a Comment Cancel reply