How to find orders that contain the same product

Question

How to find orders that contain the same product

Coriolan

SSCommitted

Points: 1931
More actions
August 27, 2013 at 12:21 pm

#146195

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!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Coriolan SSCommitted Points: 1931 More actions · Answer 2

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 SSC Guru Points: 286590 More actions · Answer 3

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Coriolan SSCommitted Points: 1931 More actions · Answer 4

Hi Sean,

The solution is perfect. Thank you very much for the help!

I learned about the stuff function from you...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

Coriolan SSCommitted Points: 1931 More actions · Answer 6

Thank you, Chris. I got to learn the CROSS APPLY from your solution.

Many thanks!

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Coriolan SSCommitted Points: 1931 More actions · Answer 8

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 SSC Guru Points: 286590 More actions · Answer 9

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. 😀

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Coriolan SSCommitted Points: 1931 More actions · Answer 10

Coriolan

SSCommitted

Points: 1931

August 29, 2013 at 9:51 pm

#1646223

Thank you Sean.

Coriolan SSCommitted Points: 1931 More actions · Answer 11

Jeff,

My apologies for being slow on understanding your question.

Your question is a valid question. I am doing database development for the application teams. The teams services that take the "result set" from my sprocs and uses C# code to serialize the result set into an xml message to send over the network to other services.

They prefer having a result set of 1 row with multiple columns.

One of the columns happens to be the comma delimited string that I had asked for help. Both teams ( the sender service and the receiver service) prefer having:

...

to easily shred the xml message.

Needless to say that the comma delimited string will be split to a table for processing....

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

In that case, why not just return the value in the XML format that they want? It's pretty easy to do. In fact, the solution that Sean posted for the CSV format is just about there.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Coriolan SSCommitted Points: 1931 More actions · Answer 13

Yes, I can return data in xml format. The same sproc output is used by the app UI code and the app service code. App UI needs data in "ready to use" state to decide where and what to render. App service code sometimes is based on XML or JSON serialization.