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
4400006 not both.