Take the 2-minute tour ×

Title might not be massively helpful on this one so I'll try and explain it as best I can.

Assume two tables.

Product (ProductID, Name)
ProductCategory (ID, ProductID, CategoryID)

and a third temporary table containing a list of CategoryIDs to match.


I want to use the contents of MatchTable to return the products that have all of the associated categories. IE.

Product 1: Associated with categories 1 and 2.
Product 2: Associated with categories 2 and 3.
Product 3: Associated with categories 1, 2 and 3.

If MatchTable contains 1 and 2, I want to return products 1 and 3 because they match the criteria. If MatchTable contains 2 then all products would be returned.

The code for returning a product that matches any of the values in MatchTable is easy, but I can't seem to get the syntax right for the products that match all.

share|improve this question
lets see what you have so far. –  BigRabbit Mar 13 at 13:19
This is usually referred to as "relational division". –  Damien_The_Unbeliever Mar 13 at 13:41
Seems this question is very similar to what I'm looking for: stackoverflow.com/questions/4019343/… I will look there too. –  Textuality Mar 13 at 13:42

1 Answer 1

up vote 2 down vote accepted

Guess I'd test a match count vs rows in the matchtable

select p.ProductID, max(p.Name)
from Product p
inner join ProductCategory c on c.ProductID = p.ProductID
inner join MatchTable m on m.CategoryID = c.CategoryID
group by p.ProductID
having COUNT(*) = (select COUNT(*) from MatchTable)

See fiddle

share|improve this answer
That seems to work. Thank you. –  Textuality Mar 13 at 13:48

Your Answer


