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

jxharding
jxharding used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Awarded 2012

Commented:
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

Author

Commented:
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
Top Expert 2012
Commented:
Something like this perhaps:
SELECT  OrderID
FROM    YourTable
WHERE   UserName = 'John'
        AND ProductID IN ('ProductA', 'ProductB', 'ProductC', 'ProductD')
HAVING  COUNT(DISTINCT ProductID) = 4

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial