asked on
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.