2

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:

  1. 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 has Product A
  2. There can also be situations with many items that need to be requested, not just 2 as it in this case.
  3. 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?

Improve this question

3 Answers 3

Reset to default
2

This is a typical example of Relational Division With Remainder, where the number of divisors is unknown.

There are a number of ways to cut this, but fundamentally, the key to solving this neatly is to put your input data into tabular form. This could be a temp table, table variable or Table Valued Parameter.

Here is one typical Relational Division solution. Note that the OUTER APPLY could also be done with a grouped LEFT JOIN.

DECLARE @input TABLE (item varchar(20) PRIMARY KEY, quantity int); INSERT @input VALUES ('Product A',20),('Product B',NULL); SELECT o.id FROM Order_tbl o WHERE EXISTS (SELECT 1 FROM @input i OUTER APPLY ( SELECT ol.item FROM OrderLine ol WHERE ol.item = i.item AND o.id = ol.order_id GROUP BY ol.item HAVING SUM(ol.qty) > i.quantity OR i.quantity IS NULL ) ol HAVING COUNT(ol.item) = COUNT(*) );

Another method is a double NOT EXISTS although this is commonly not efficient.

SELECT o.id FROM Order_tbl o WHERE NOT EXISTS (SELECT 1 FROM @input i WHERE NOT EXISTS (SELECT 1 FROM OrderLine ol WHERE ol.item = i.item AND o.id = ol.order_id GROUP BY ol.item HAVING SUM(ol.qty) > i.quantity OR i.quantity IS NULL ) );

A final option, which is partially used by one of the other answers, is to pre-calculate the count of input values, and then use a normal join. This is often the most efficient.

Again the INNER JOIN could be a CROSS APPLY.

DECLARE @count int = (SELECT COUNT(*) FROM @input); SELECT o.id FROM Order_tbl o WHERE EXISTS (SELECT 1 FROM ( SELECT ol.item, ol.order_id, SUM(ol.qty) totalQty FROM OrderLine ol GROUP BY ol.item, ol.order_id ) ol JOIN @input i ON ol.item = i.item AND o.id = ol.order_id HAVING COUNT(*) = COUNT(CASE WHEN ol.totalQty > i.quantity OR i.quantity IS NULL THEN 1 END) AND COUNT(*) = @count );

db<>fiddle

Improve this answer
0
0

You can use having count with sum.

Try:

select o.id,o.total from Order_tbl o inner join ( select order_id from OrderLine where item in ('Product A','Product B') group by order_id having count(distinct item)=2 and sum(case when item = 'Product A' then qty end ) > 20 ) as ol on o.id=ol.order_id;

having count(distinct item)=2 will select only the order_id which have both Product A and Product B values. If you would add new products , include the new one on the where condition and increase 2 with the total number of products .

sum(case when item = 'Product A' then qty end ) > 20 will return only the records where total SUM of Product A > 20

Testing fiddle

Improve this answer
0

use a GROUP BY query and place the required condition in the HAVING clause

Condition : Orders which contain both Product A and Product B

having sum(case when l.item = 'Product A' then 1 else 0 end) > 0 and sum(case when l.item = 'Product B' then 1 else 0 end) > 0

Condition : Orders which have more than 20 units of Product A in total (across all lines).

sum(case when l.item = 'Product A' then qty else 0 end) >= 20

Query:

select o.id from [Order] o inner join [OrderLine] l on o.id = l.order_id group by o.id having sum(case when l.item = 'Product A' then 1 else 0 end) > 0 and sum(case when l.item = 'Product B' then 1 else 0 end) > 0 and sum(case when l.item = 'Product A' then qty else 0 end) >= 20
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.