SQL – Finding Orders with only specific items and show it by product

0

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

I found that order #101, #103, #106 and #107 are the orders which contain product A or B or C only. But I want to show it by product

the expected result is:

product    qty
A          3      (explanation: order #101, #103, #106)
B          3      (explanation: order #101, #106, #107)
C          1      (explanation: order #101)  

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 is still showing the product individually as I identify these orders by grouping the order id

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

Thank you for your help!!!



Read more here: https://stackoverflow.com/questions/67923825/sql-finding-orders-with-only-specific-items-and-show-it-by-product

Content Attribution

This content was originally published by uyeahu at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: