SQL - Finding Orders with only specific items

0
votes

I would like to find out the orders which contain one or more than one product: A, B, C.

the expected result is:

order_id
101              (explanation: product A, B, C)
103              (explanation: product A)
106              (explanation: product A, B)
107              (explanation: product B)

Orders

id deleted_at
101 null
102 null
103 null
104 null
105 5-5-2021
106 null
107 null

Order_items

id order_id product
1 101 A
2 101 A
3 101 B
4 101 C
5 102 A
6 102 D
7 103 A
8 104 D
9 105 D
10 105 B
11 106 A
12 106 B
13 107 B
14 107 B
15 107 B

I've tried to write the code like this but it still contains other irrelevant orders.

select Orders.id
from Orders left join Order_items on Orders.id=Order_items.order_id
where Orders.deleted_at is null
group by Orders.id
having count(distinct(case 
when Order_items.product=A then 1
when Order_items.product=B then 1
when Order_items.product=C then 1 end)) between 1 and 3;
2
mysqlsqlcombinationscalculation

2 Answers

1
votes

You can count the products that are not A, B, or C and be sure none of them are in the order:

select o.id
from Orders o join
     Order_items oi
     on o.id = oi.order_id
where o.deleted_at is null
group by o.id
having sum( oi.product not in ('A', 'B', 'C') ) = 0;

Note that this uses MySQL's convenient shorthand for counting boolean matches.

0
votes

Just check if they exist, no joins required.

SELECT
    id
FROM
    orders o
WHERE
    EXISTS (
        SELECT
            NULL
        FROM
            order_items oi
        WHERE
            oi.order_id = o.id
            AND oi.product IN (
                'A',
                'B',
                'C'
            )
    )