Search code examples
sqlt-sqlrelational-division

Simple SQL Select with all matches

SQL question: I have table with two columns: ProductId and CatId

I want to select products that belong to all of the passed in CatIds.

For example this is my table:

ProductID        CatID
_____________________
1129                2
1129                3
1129                4
1130                2
1130                3

Now if I pass 2,3,4 the result should be 1129.

If I pass 2,3 the result should be 1130 (not 1129, as it should belong to all of the CatIds passed, no less - no more)

Any idea how this can be achieved? IN with AND can not work. IN with OR will not serve the purpose.

Thanks.

Solution 
  • SELECT ProductID
FROM Table1 a
WHERE CatID IN (2,3)
GROUP BY ProductID
HAVING COUNT(*) =
        (
          SELECT COUNT(*)
          FROM table1 b
          WHERE b.ProductID = a.ProductID
        ) AND 
        COUNT(*) = 2