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

WHERE (P.PART_DESCRIPTION = 'GasRange' AND P.PART_DESCRIPTION = 'Washer') ? — Erran Morad
– Erran Morad, Commented Apr 4, 2014 at 18:43
I tried this prior to submitting and it did not work. Thx though! — dolm77
– dolm77, Commented Apr 4, 2014 at 19:02

Tomas Pastircak · Accepted Answer · 2014-04-04 19:09:25Z

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 ;)

edited Apr 4, 2014 at 19:09

answered Apr 4, 2014 at 18:42

Tomas Pastircak

2,85718 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dolm77 Over a year ago

Your query works! (Thx by the way) But I'd like to simplify. I edited my issue and listed the tables and fields.

dolm77 Over a year ago

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

SoulTrain · Accepted Answer · 2014-04-04 18:50:02Z

0

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

answered Apr 4, 2014 at 18:50

SoulTrain

1,9041 gold badge12 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Query on SQL Server 2008

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related