SQL statement to select group containing all of a set of values

Question

In SQL Server 2005, I have an order details table with an order id and a product id. I want to write a sql statement that finds all orders that have all the items within a particular order. So, if order 5 has items 1, 2, and 3, I would want all other orders that also have 1, 2, and 3. Also, if order 5 had 2 twice and 3 once, I'd want all other orders with two 2s and a 3.

My preference is that it return orders that match exactly, but orders that are a superset are acceptable if that's much easier / performs much better.

I tried a self-join like the following, but that found orders with any of the items rather than all of the items.

SELECT * FROM Order O1
JOIN Order O2 ON (O1.ProductId = O2.ProductId)
WHERE O2.OrderId = 5

This also gave me duplicates if order 5 contained the same item twice.

Answer 1

If the OrderDetails table contains a unique constraint on OrderId and ProductId, then you can do something like this:

Select ...
From Orders As O
Where Exists    (
                Select 1
                From OrderDetails As OD1
                Where OD1.ProductId In(1,2,3)
                    And OD1.OrderId = O.Id
                Group By OD1.OrderId
                Having Count(*) = 3
                )

If it is possible to have the same ProductId on the same Order multiple times, then you could change the Having clause to Count(Distinct ProductId) = 3

Now, given the above, if you want the situation where each order has the same signature with duplicate product entries, that is trickier. To do that you would need the signature of order in question over the products in question and then query for that signature:

With OrderSignatures As
    (
    Select O1.Id
        ,   (
            Select '|' + Cast(OD1.ProductId As varchar(10))
            From OrderDetails As OD1
            Where OD1.OrderId = O1.Id
            Order By OD1.ProductId
            For Xml Path('')
            ) As Signature
    From Orders As O1
    )
Select ...
From OrderSignatures As O
    Join OrderSignatures As O2
        On O2.Signature = O.Signature
            And O2.Id <> O.Id
Where O.Id = 5