Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to find orders that contain the same product Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 12:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
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!
Post #1488895
Posted Tuesday, August 27, 2013 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1488897
Posted Tuesday, August 27, 2013 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
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!
Post #1488910
Posted Tuesday, August 27, 2013 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1488917
Posted Tuesday, August 27, 2013 6:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
Hi Sean,

The solution is perfect. Thank you very much for the help!
I learned about the stuff function from you...
Post #1488996
Posted Wednesday, August 28, 2013 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1489054
Posted Wednesday, August 28, 2013 2:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
Thank you, Chris. I got to learn the CROSS APPLY from your solution.

Many thanks!

Post #1489415
Posted Wednesday, August 28, 2013 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489504
Posted Thursday, August 29, 2013 2:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 133, Visits: 308
Jeff,
I am not sure why the question was about CSV format? My question is to get a comma delimited string as output.
Post #1489893
Posted Thursday, August 29, 2013 2:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1489895
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse