How do I find an order that contains all the available products?

I have a products table that contains all the available products, as well as the order_has_product table which contains all the products that should be included in every order.

How do I find the order_id for any order that contains all the available products?

Note how the order with an order_id of 1 contains all the available products.

order_has_product:

order_id	product_id
1	7
1	8
1	9
2	7
3	8

products:

product_id	name
7	shovel
8	rake
9	scythe

This is what I have so far:

SELECT order_id
FROM order_has_product
WHERE product_id = ALL (
    SELECT product_id
    FROM products
);

Answer

This is another way by using GROUP_CONCAT:

SELECT order_id FROM order_has_product 
GROUP BY order_id
HAVING GROUP_CONCAT(DISTINCT product_id)=(SELECT GROUP_CONCAT(product_id) FROM products)
ORDER BY product_id;

This query compares the concatenated product id’s which will check if an id contains all the product_id’s listed in the products table.

RESULT

order_id  
----------
         1

MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group

Check MySQL GROUP_CONCAT() function for more details and examples of GROUP_CONCAT.