Determine if a supplier supplies all products in a list

Question

I have a table SupplierCatalog (idProduct, idSupplier). A product can be supplied by multiple suppliers; and a supplier supplies multiple products (many-to-many relation).

I want to check if a supplier supplies all products in a list, for example, the products to check are (10, 26, 40, 42, 50) and the supplier is 5.

I tried:

SELECT idproduct 
FROM SupplierCatalog 
where idProduct IN (10, 26, 40, 42, 50) AND idSupplier = 5

and then check in C# if number of rows returned equal the number of products passed in the query, it works but it doesn't look professional to me.

Does anyone have a clue on how to obtain the result I want, I just one a TRUE if all match or FALSE if at least one didn't match.

Answer 1

Assuming that idProduct, idSupplier are unique*, here is a simple query:

SELECT 1
FROM SupplierCatalog
WHERE idProduct IN (10, 26, 40, 42, 50) AND idSupplier = 5
HAVING COUNT(*) = 5 -- this is the number of items in the above list

This returns 0 or 1 rows which is all you need to check in your code.

_{* If duplicates are allowed/present change to HAVING COUNT(DISTINCT idProduct) ...}

Answer 2

SELECT idproduct 
  FROM SupplierCatalog 
 WHERE idProduct IN (10, 26, 40, 42, 50) 
   AND idSupplier = 5 
 GROUP BY idproduct 
HAVING COUNT(*) = 5

0 rows is false and 1 row is true

You can remove the AND idSupplier = 5 and get all the suppliers