Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.
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 today
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.
Active 1 day ago
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
Active 1 day ago
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
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
- Software-Other
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona, *Monitoring tool
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
- Server Hardware
- IT Administration
- Disaster Recovery
- Storage Software
- Databases
- Storage Hardware, MySQL Server, Hardware, Percona, *Backups
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month7 days, 14 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.