# Find an Order that contains two products

#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
``````