As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Advanced Select
Posted on 2013-01-02
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
4 Comments
Active 1 day ago
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.
I'm not seeing that Master is needed at all?
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
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
Curious, to better my answers in the future, why was my solution given a B? I presume you only had to copy paste it?
Featured Post
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
- Databases
- AWS
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 17 hours left to enroll
622 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.