SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to find orders that contain the same product


How to find orders that contain the same product

Author
Message
Coriolan
Coriolan
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 370
I have a requirement to find all orders that contain the same product. This same product is not defined upfront.

declare @Orderdetail table
(OrderID int,
ProductID int)
INsert into @OrderDetail Values ( 1, 100)
INsert into @OrderDetail Values ( 1, 101)
INsert into @OrderDetail Values ( 2, 100)
INsert into @OrderDetail Values ( 2, 200)
INsert into @OrderDetail Values ( 3, 300)

In the above sample data, my expected result set would be a list of Order IDs namely( 1, 2) because Orders 1 and 2 contain product 100.
If I have 3 new orders say Orders 10, 20, 30 that contain product 500.
my result set will be 1,2 for product 100, and 10, 20, 30 for product 500.
If I have a new order 100 that contains both product 100 and 500
my result set will be 1,2,100 for product 100, 10, 20, 30 , 100 for product 500.

Can you suggest how to approach this query?

Thank you!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26293 Visits: 17553
Coriolan (8/27/2013)
I have a requirement to find all orders that contain the same product. This same product is not defined upfront.

declare @Orderdetail table
(OrderID int,
ProductID int)
INsert into @OrderDetail Values ( 1, 100)
INsert into @OrderDetail Values ( 1, 101)
INsert into @OrderDetail Values ( 2, 100)
INsert into @OrderDetail Values ( 2, 200)
INsert into @OrderDetail Values ( 3, 300)

In the above sample data, my expected result set would be a list of Order IDs namely( 1, 2) because Orders 1 and 2 contain product 100.
If I have 3 new orders say Orders 10, 20, 30 that contain product 500.
my result set will be 1,2 for product 100, and 10, 20, 30 for product 500.
If I have a new order 100 that contains both product 100 and 500
my result set will be 1,2,100 for product 100, 10, 20, 30 , 100 for product 500.

Can you suggest how to approach this query?

Thank you!


Are you looking for all products that have been on at least two orders? Your description is not very clear.

Maybe something like:


select ProductID, COUNT(*)
from @Orderdetail
group by ProductID
having COUNT(*) > 1



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Coriolan
Coriolan
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 370
Thank you for asking for clarifications. I was definitively unclear.

What I would like is a comma delimited string of order IDs that share the same product.

So when Order 1 contains product 100, and Order 2 also contains product 100,

I would like to have a table of 2 columns ( productID, string of Order IDs with that product IDs)

so it will be
Product ID Relevant List of Order IDs
_________________________________________________________

100 1, 2
500 10, 20, 30

Thanks!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26293 Visits: 17553
OK. I created data for the last of your sample data.


INsert into @OrderDetail Values ( 10, 500)
INsert into @OrderDetail Values ( 20, 500)
INsert into @OrderDetail Values ( 30, 500)



Now to retrieve what you are looking for. First I got the list of products with duplicates. Then I get the orders associated with those products. Last but not least generate the delimited list from the values.


;with Products
as
(
select ProductID, COUNT(*) as MyCount
from @Orderdetail
group by ProductID
having COUNT(*) > 1
)
, ProductOrders as
(
select o.OrderID, o.ProductID
from @Orderdetail o
join Products p on o.ProductID = p.ProductID
)

select p1.ProductID,
STUFF((select ',' + cast(OrderID as varchar(5))
from ProductOrders p2
where p2.ProductID = p1.ProductID
order by p2.ProductID
for xml path('')), 1, 1, ' ') as OrderList
from ProductOrders p1
group by p1.ProductID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Coriolan
Coriolan
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 370
Hi Sean,

The solution is perfect. Thank you very much for the help!
I learned about the stuff function from you...
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16406 Visits: 19554
Here's another way:
SELECT p.ProductID, MAX(x.OrderList)
FROM @OrderDetail p
CROSS APPLY (
SELECT STUFF(
(SELECT ',' + CAST(o.OrderID AS varchar(5))
FROM @OrderDetail o
WHERE o.ProductID = p.ProductID
ORDER BY o.OrderID
FOR XML PATH(''))
, 1, 1, ' ') AS OrderList
) x
GROUP BY p.ProductID
HAVING COUNT(*) > 1



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Coriolan
Coriolan
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 370
Thank you, Chris. I got to learn the CROSS APPLY from your solution.

Many thanks!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87026 Visits: 41112
I have to ask... why do you need this with the CSV format?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Coriolan
Coriolan
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 370
Jeff,
I am not sure why the question was about CSV format? My question is to get a comma delimited string as output.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26293 Visits: 17553
Coriolan (8/29/2013)
Jeff,
I am not sure why the question was about CSV format? My question is to get a comma delimited string as output.


CSV = Comma Separated Values. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search