How to get the products that all Warehouse exist?

Question

I have a question as the title,I wanna get the products which appeared in every Warehouse, I have no idea when i thought long time,i found i am so beetleheaded,

Thare are three tables in my sql server database:

Product ( productID, name,model, size, color )

Warehouse ( warehouseID, name, address )

Inventory ( warehouseID, productID, quantity )

i hope someone help me to have a look and can write the sql that get the result

Thank you.

Answer 1

Use a GROUP BY to count the number of warehouses each product is in. Accept those rows for which this count is equal to the total number of warehouses.

SELECT productID
FROM Inventory
WHERE quantity > 0
GROUP BY productID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Warehouse)

This assumes that (productID, warehouseID) is unique in the inventory table. If it is not then use COUNT(DISTINCT warehouseID) instead of COUNT(*).