Avatar of jxharding
jxharding
 asked on

identify duplicate order based on products ordered (e.g. John already orderd ProdA+B+C previously)

Hi, I require help in finding a duplicate order based on detail lines please.
e.g. John ordered ProductA + ProductB + ProductC + ProductD on one order.
I need to inform him if he attempts to order the combination of ProductA + ProductB + ProductC + ProductD again.

Attached is a horrible attempt. It is incorrect. I dont know of better.
I tried to summarize each order into one line
e.g.
OrderID         Products
1                    ProductA,ProductB,ProductC,ProductD
2                    ProductB,ProductC,ProductE

but it is terrible - im using a cursor and in the real app there are thousands of records so i wont be able to use it. Please assist
sql-order.txt
Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Rainer Jeschor

Hi,
this will return the list of products without cursor:
DECLARE @productlist varchar(1000)
SELECT @productlist = coalesce(@productlist + ',','') +CAST(ProductID AS varchar(10)) FROM dbo.[Order Details]
WHERE OrderID = 10248
SELECT @productlist

Open in new window

jxharding

ASKER
Hi, thanks, I need to find a loop through all the orders, and find ones with the exact same products that the user has entered , e.g. Product A,B,C,D

I dont know what the OrderID is unfortunately, and cant use the above script even though I want to
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes