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

Solution 1:

You were close

SELECT order_id
FROM orders
WHERE product_id in (222,555) 
GROUPBY order_id
HAVINGCOUNT(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.

Solution 2:

I have a preference for doing set comparisons only in the having clause:

select order_id
from orders
groupby order_id
havingsum(casewhen product_id =222then1else0end) >0andsum(casewhen product_id =555then1else0end) >0

What this is saying is: get me all orders where the order has at least one product 222 and at least one product 555.

I prefer this for two reasons. The first is generalizability. You can arrange more complicated conditions, such as 222 or 555 (just by changing the "and" to and "or"). Or, 333 and 555 or 222 without 555.

Second, when you create the query, you only have to put the condition in one place, in the having clause.

Solution 3:

Assuming your database is properly normalized, i.e. there's no duplicate Product on a given Order

Mysqlism:

select order_id
from orders
groupby order_id
having sum(product_id in (222,555)) = 2

Standard SQL:

select order_id
from orders
groupby order_id
havingsum(casewhen product_id in (222,555) then1end) =2

---

If it has duplicates:

CREATETABLE tbl
    (`order_id` int, `product_id` int)
;

INSERTINTO tbl
    (`order_id`, `product_id`)
VALUES
    (1, 222),
    (1, 555),
    (2, 333),
    (1, 555)
;

Do this then:

select order_id
from tbl
groupby order_id
havingcount(distinctcasewhen product_id in (222,555) then product_id end) =2

Live test: http://www.sqlfiddle.com/#!2/fa1ad/5

Solution 4:

CREATETABLE orders
        ( order_id INTEGERNOTNULL
        , product_id INTEGERNOTNULL
        );
INSERTINTO orders(order_id,product_id) VALUES
 (1, 222 ) , (1, 555 ) , (2, 333 )
, (3, 222 ) , (3, 555 ) , (3, 333 ); -- order#3 has all the productsCREATETABLE products AS (SELECTDISTINCT product_id FROM orders);

SELECT*FROM orders o1
   ---- There should not exist a product-- that is not part of our order.--WHERENOTEXISTS (
        SELECT*FROM products pr
        WHERE1=1-- extra clause: only want producs from a literal listAND pr.product_id IN (222,555,333)
           --  ... that is not part of our order...ANDNOTEXISTS ( SELECT*FROM orders o2
                WHERE o2.product_id = pr.product_id
                AND o2.order_id = o1.order_id
                )
        );

Result:

 order_id | product_id 
----------+------------
        3 |        222
        3 |        555
        3 |        333
(3 rows)