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

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
);

CodePudding user response：

ALL means that the condition will be true only if the operation is true for all values in the range.

so that ALL = might not you expect, but we can try to use >= which will match with the biggest product_id of order_has_product.

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

otherwise, If I understand correctly from your logic, we can try to use EXISTS subquery to make it.

SELECT ohp.order_id
FROM (
  SELECT order_id,COUNT(DISTINCT product_id) cnt
  FROM order_has_product ohp
  WHERE EXISTS (SELECT 1 FROM products p WHERE p.product_id = ohp.product_id)
  GROUP BY order_id
) ohp
WHERE EXISTS (
   SELECT 1 
   FROM products p    
   HAVING COUNT(*) = ohp.cnt
)

sqlfiddle

CodePudding user response：

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.