Link to home
Start Free TrialLog in
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
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

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

Avatar of jxharding
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
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial