Search code examples
mysqlsqlrelational-division

How to find if a list/set is contained within another list

I have a list of product IDs and I want to find out which orders contain all those products. Orders table is structured like this:

order_id | product_id
----------------------
1        | 222
1        | 555
2        | 333

Obviously I can do it with some looping in PHP but I was wondering if there is an elegant way to do it purely in mysql. My ideal fantasy query would be something like:

SELECT order_id
FROM orders
WHERE (222,555) IN GROUP_CONCAT(product_id)
GROUP BY order_id

Is there any hope or should I go read Tolkien? :) Also, out of curiosity, if not possible in mysql, is there any other database that has this functionality?

Solution

  • You were close

    SELECT order_id
FROM orders
WHERE product_id in (222,555) 
GROUP BY order_id
HAVING COUNT(DISTINCT product_id) = 2

    Regarding your "out of curiosity" question in relational algebra this is achieved simply with division. AFAIK no RDBMS has implemented any extension that makes this as simple in SQL.