SQL Advanced Select
trying to identify orders that contain a specific product (but only purchased with another product)
There are other fields in each table but are irrelevant for this example
Sample Data:
master table
----------------------
order_no | order_date | customer_id | amount
100 | 2013-01-01 | 500 | 20.00
101 | 2013-01-01 | 502 | 22.00
102 | 2013-01-01 | 504 | 21.00
103 | 2013-01-01 | 505 | 22.00
order_details table
--------------------------
orderdetails_id | order_no | product_desc
1 | 100 | prod1
2 | 100 | prod2
3 | 100 | prod3
4 | 101 | prod2
5 | 102 | prod1
6 | 103 | prod3
7 | 103 | prod2
I would like to get all records and the amount of products that contain prod2 (which was ONLY purchased with another product)
Expected results:
order_no | prod_count
100 | 3
103 | 2
There are other fields in each table but are irrelevant for this example
Sample Data:
master table
----------------------
order_no | order_date | customer_id | amount
100 | 2013-01-01 | 500 | 20.00
101 | 2013-01-01 | 502 | 22.00
102 | 2013-01-01 | 504 | 21.00
103 | 2013-01-01 | 505 | 22.00
order_details table
--------------------------
orderdetails_id | order_no | product_desc
1 | 100 | prod1
2 | 100 | prod2
3 | 100 | prod3
4 | 101 | prod2
5 | 102 | prod1
6 | 103 | prod3
7 | 103 | prod2
I would like to get all records and the amount of products that contain prod2 (which was ONLY purchased with another product)
Expected results:
order_no | prod_count
100 | 3
103 | 2
Asked:
Who is Participating?
Give this a whirl (air code, so modify to suit your needs)..
SELECT od.order_no, Count(od.product_desc) as details_count
FROM order_details od
JOIN (SELECT DISTINCT order_no FROM order_details WHERE product_desc <> 'prod2') odc ON od.order_no = odc.order_no
WHERE product_desc = 'prod2'
GROUP BY order_no
The idea here is that you're JOINing on a table that has everything but prod2's, so if there is no prod2 in an order, the JOIN will not display the record.
SELECT od.order_no, Count(od.product_desc) as details_count
FROM order_details od
JOIN (SELECT DISTINCT order_no FROM order_details WHERE product_desc <> 'prod2') odc ON od.order_no = odc.order_no
WHERE product_desc = 'prod2'
GROUP BY order_no
The idea here is that you're JOINing on a table that has everything but prod2's, so if there is no prod2 in an order, the JOIN will not display the record.
Curious, to better my answers in the future, why was my solution given a B? I presume you only had to copy paste it?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
SELECT Order_No, Count(*) As Prod_Count
FROM Order_Details
WHERE Order_No IN (SELECT Order_No FROM Order_Details WHERE Product_Desc ='prod2')
GROUP BY Order_No
HAVING Count(*)>1