I have the following tables, Order and OrderLine:
Order:
id | total
----------
1 | 55.09
2 | 62.42
OrderLine:
order_id | line_number | item | qty
----------------------------------------
1 | 1 | Product A | 50
1 | 2 | Product B | 15
2 | 1 | Product A | 23
I am looking to construct a query that will select all Orders which contain both Product A and Product B.
Some caveats:
- There may be multiple lines that have Product A and Product B. For instance, there could be a third line in the
order 1
that hasProduct A
- There can also be situations with many items that need to be requested, not just 2 as it in this case.
- I am also looking to query by total quantity. So, for example, only orders which have more than 20 units of Product A in total (across all lines).
My first thought was do an inner join per item, so something like:
SELECT
T0.id
FROM
Order T0
INNER JOIN OrderLine T1 on T1.order_id = T0.id AND T1.ItemCode = 'Product A'
INNER JOIN OrderLine T2 on T1.order_id = T0.id AND T2.ItemCode = 'Product B'
GROUP BY
T0.id
However, I'm not sure how to extend this to have the ability to select based on total quantity. Possibly using SUM and HAVING?