Customers who have ordered every product in a given category
Tables orders(customer_id, product_id) and products(id, category_id). For a given category_id, return the customers who have ordered every product in that category — a relational-division problem. A customer missing even one product must be excluded.
-- customers who ordered ALL products of a given categoryWrite the query.
Group each customer's category orders and keep those whose distinct product count equals the category's total — HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products WHERE category_id = :cat). DISTINCT stops repeat orders inflating the count.
- ✗Using IN, which returns customers who ordered any product, not all
- ✗Counting without DISTINCT so repeat orders inflate the match
- ✗Comparing with >= instead of = and letting extra products through
- →How would a double-NOT-EXISTS formulation express the same division?
- →Why is DISTINCT essential when a customer can reorder the same product?
This is a relational-division query: find the customers whose set of ordered products contains the whole set of a category's products. The count-based formulation is the clearest:
SELECT o.customer_id
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE p.category_id = :cat
GROUP BY o.customer_id
HAVING COUNT(DISTINCT o.product_id)
= (SELECT COUNT(*) FROM products WHERE category_id = :cat);Restricting the join to the category means each customer's group holds only their category orders. COUNT(DISTINCT o.product_id) is how many distinct category products they bought; it must equal the category's total product count. DISTINCT is essential — without it, a customer who reordered one product would count it twice and could match on >= while still missing another product. An equivalent double-NOT EXISTS phrasing ("no product of the category that the customer has not ordered") returns the same set.
