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)
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!!!