SQL Query on SQL Server 2008

Question

I'm trying to get only customers that ordered both a "Gas Range" and a "Washer". I'm getting Customers who ordered a "Gas Range" and not a "Washer" and customers with both. I need the customer that meets both conditions. I'm close but a little stuck. Below is the query that I have so far. Please let me know if you need more information.

My Tables - CUSTOMER(CUST_NUM, CUST_NAME), ORDER_LINE(ORDER_NUM, PART_NUM), ORDERS(ORDER_NUM, CUST_NUM), PART(PART_NUM, PART_DESCRIPTION)

SELECT C.CUST_NAME AS [Customer(s) that ordered a Gas Range and Washer]
FROM CUSTOMER C
INNER JOIN ORDERS O
ON C.CUST_NUM = O.CUST_NUM
INNER JOIN ORDER_LINE OL
ON O.ORDER_NUM = OL.ORDER_NUM
INNER JOIN PART P
ON OL.PART_NUM = P.PART_NUM 
WHERE P.PART_DESCRIPTION IN ('GasRange','Washer')
GROUP BY C.CUST_NAME

Answer 1

try the following

SELECT C.CUST_NAME AS [Customer(s) that ordered a Gas Range and Washer]
FROM CUSTOMER C
INNER JOIN ORDERS O
ON C.CUST_NUM = O.CUST_NUM
INNER JOIN ORDER_LINE OL
ON O.ORDER_NUM = OL.ORDER_NUM
INNER JOIN PART P
ON OL.PART_NUM = P.PART_NUM 
INNER JOIN ORDERS O2
ON C.CUST_NUM = O2.CUST_NUM
INNER JOIN ORDER_LINE OL2
ON O2.ORDER_NUM = OL2.ORDER_NUM
INNER JOIN PART P2
ON OL2.PART_NUM = P2.PART_NUM 
WHERE P.PART_DESCRIPTION IN ('GasRange') and P2.PART_DESCRIPTION IN ('Washer')
GROUP BY C.CUST_NAME

EDIT: Had a further look and I'm afraid that this can't be simplified in any other way than using WITH and complicated aggregate functions, which I would say would be more complicated than this - I think the other solution suggested using WITH won't work - it joins incorrectly. You definitely can't remove order line, and you have to use the order twice as well - if it was used once, it will cover only when the customer ordered it within one order, which is not what you wanted ;)

Answer 2

Try this...

So basically you need to join your Parts table again to ensure the same customer ordered a "Gas Range" and a "Washer". An IN, like in your current query functions as an OR therefore you are not getting the expected result.

WITH CTE AS (
SELECT DISTINCT O.CUST_NUM FROM ORDERS O
INNER JOIN ORDER_LINE OL
ON O.ORDER_NUM = OL.ORDER_NUM
INNER JOIN PART P
ON OL.PART_NUM = P.PART_NUM 
INNER JOIN PART P2
ON OL.PART_NUM = P2.PART_NUM 
WHERE P.PART_DESCRIPTION IN ('GasRange')
AND P2.PART_DESCRIPTION IN ('Washer')
)
SELECT C.CUST_NAME AS [Customer(s) that ordered a Gas Range and Washer]
FROM CUSTOMER C
INNER JOIN CTE O
ON C.CUST_NUM = O.CUST_NUM