Avatar of xtreme109
xtreme109
Flag for United States of America asked on

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
MySQL ServerSQL

Avatar of undefined
Last Comment
xtreme109

8/22/2022 - Mon
SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Ryan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan

Curious, to better my answers in the future, why was my solution given a B? I presume you only had to copy paste it?
xtreme109

ASKER
I didnt realize that. sorry! The default grade was a 'b' shouldve been an A
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck