Solved

SQL Advanced Select

Posted on 2013-01-02
4
270 Views
Last Modified: 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
0
Comment
Question by:xtreme109
[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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 38736850
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.
0
 
LVL 13

Accepted Solution

by:
Ryan earned 350 total points
ID: 38736851
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
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38737031
Curious, to better my answers in the future, why was my solution given a B? I presume you only had to copy paste it?
0
 
LVL 2

Author Comment

by:xtreme109
ID: 38737145
I didnt realize that. sorry! The default grade was a 'b' shouldve been an A
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question