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

user_14794055 0
Viewed 47

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
);
mysql sql
3 Answers

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

user_51760710

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.

user_153523250

If by How do I find the order_id for any order that contains all the available products you meant to find the individual order_id which buys everything in your products table (in other words, big spender order_id), others have provided you with answers. However, if you want to find whether an individual order_id has a product_id that is not in the products table(in other words, whether or not has everything ready to ship), I can help.

create table order_has_product (order_id int,product_id int);
insert order_has_product values(1,7),(1,8),(1,9),(2,7),(3,8);
create table products (product_id int,name varchar(10));
insert products values(7,'shovel'),(9,'scythe'); -- order_id 8 is removed from the table for better demonstration

select order_id,
min(if(product_id in (select product_id from products),'yes','no')) as has_everything_ready 
from order_has_product
group by order_id 
;
user_185543690
Related