SQL - Return rows matching all values from a joine

问题:

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.

MatchTable(CategoryID)

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.

回答1:

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