Find an Order that contains two products

Transact-SQL
1

Hi

I am trying to find all orders that contain two specific products, yet all I seem to keep getting are orders that contain one or the other item.

This is my code so far

SELECT t1.order_no, t2.part_no, sum(t2.qty)
FROM dba.order_header as t1 
LEFT JOIN dba.order_lines as t2 ON t1.order_no = t2.order_no
WHERE EXISTS ( 
SELECT x1.order_no
FROM dba.order_header as x1 
LEFT JOIN dba.order_lines as x2 ON x1.order_no = x2.order_no
WHERE x1.order_no = t1.order_no
AND x2.part_no = '8400001'
GROUP BY x1.order_no )
AND x2.part_no = '440006'
GROUP BY t1.order_no

My understanding (which is basic) is that my EXIST will find all the orders which have part number 8400001, then the main query will search these orders for any that also contain part number 4400006. But what I seem to get is order that contain either 8400001 or 4400006 not both.

2

Perhaps this?

SELECT
	t1.Order_no,
	t2.part_no,
	SUM(t2.Qty)
FROM
	dba.order_header AS t1
	INNER JOIN dba.order_lines AS t2 ON
		t1.order_no = t2.order_no
WHERE
	t2.part_No IN ('8400001','440006')
GROUP BY
	t1.order_no,
	t2.part_No
HAVING
	SUM(CASE WHEN t2.part_no = '8400001' THEN t2.Qty ELSE 0 END) > 0
	AND SUM(CASE WHEN t2.part_no = '440006' THEN t2.Qty ELSE 0 END) > 0
3 
SELECT	t1.order_no, t2.part_no, SUM(t2.qty)
FROM	dba.order_header AS t1 
	LEFT JOIN dba.order_lines AS t2
		ON t2.order_no = t1.order_no
WHERE	EXISTS
	( 
		SELECT	*
		FROM	dba.order_lines AS x2
		WHERE	x2.order_no = t1.order_no
			AND x2.part_no = '8400001'
	)
	AND EXISTS
	( 
		SELECT	*
		FROM	dba.order_lines AS x3
		WHERE	x3.order_no = t1.order_no
			AND x3.part_no = '440006'
	)
GROUP BY t1.order_no, t2.part_no
1 Like