SQL to select a group containing the entire set of values

Question

In SQL Server 2005, I have an order details table with an order id and product id. I want to write an sql statement that finds all orders that have all the elements in a specific order. So, if in order 5 there are items 1, 2 and 3, I would like all other orders to also have 1, 2 and 3. In addition, if in order 5 there were 2 times two and three times, I would like all other orders are two 2s and 3.

My preference is that it returns orders that match exactly, but orders that are superset are acceptable if it is much simpler / performs much better.

I tried self-connecting as shown below, but found commands with any of the elements, and not with all the elements.

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

It also gave me duplicates if in order 5 the same element was contained twice.

Answer 1

If the OrderDetails table contains a unique constraint on OrderId and ProductId, 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 you can have the same ProductId in the same order several times, then you can change the Have clause to Count(Distinct ProductId) = 3

Now, given the above, if you need a situation where each order has the same signature with duplicate product entries, this is more difficult. To do this, you will need a signature of the appropriate order over the products in question and then a request for this 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 

Answer 2

In SQL, it is very difficult to do, because SQL is designed to generate its result set at the most basic level, comparing a set of column values ​​on one row with another value. What you are trying to do is compare one column value (or multiple column values) across multiple rows with another set of multiple rows.

To do this, you need to create some kind of order signature. Strictly speaking, this cannot be done using query syntax; You will have to use some T-SQL.

 declare @Orders table ( idx int identity(1, 1), OrderID int, Signature varchar(MAX) ) declare @Items table ( idx int identity(1, 1), ItemID int, Quantity int ) insert into @Orders (OrderID) select OrderID from [Order] declare @i int declare @cnt int declare @j int declare @cnt2 int select @i = 0, @cnt = max(idx) from @Orders while @i < @cnt begin select @i = @i + 1 declare @temp varchar(MAX) delete @Items insert into @Items (ItemID, Quantity) select ItemID, Count(ItemID) from OrderItem oi join @Orders o on o.idx = @i and o.OrderID = oi.OrderID group by oi.ItemID order by oi.ItemID select @j = min(idx) - 1, @cnt2 = max(idx) from @Items while @j < @cnt2 begin select @j = @j + 1 select @temp = isnull(@temp + ', ','') + '(' + convert(varchar,i.ItemID) + ',' + convert(varchar, i.Quantity) + ')' from @Items i where idx = @j end update @Orders set Signature = @temp where idx = @i select @temp = null end select o_other.OrderID from @Orders o join @Orders o_other on o_other.Signature = o.Signature and o_other.OrderID <> o.OrderID where o.OrderID = @OrderID 

This assumes (based on the wording of your question) that ordering multiple items of the same order in order will result in multiple rows, not the Quantity column. If so, simply remove group by from the @Items and replace Count(ItemID) with Quantity .

Answer 3

I think this should work. I use 108 as an example of OrderID, so you have to replace it twice below or use a variable.

 WITH TempProducts(ProductID) AS ( SELECT DISTINCT ProductID FROM CompMarket WHERE OrderID = 108 ) SELECT OrderID FROM CompMarket WHERE ProductID IN (SELECT ProductID FROM TempProducts) AND OrderID != 108 GROUP BY OrderID HAVING COUNT(DISTINCT ProductID) >= (SELECT COUNT(ProductID) FROM TempProducts) 

This uses the CTE to retrieve the list of Order products, and then selects all the order IDs that have everything on that list. To ensure that the returned Orders have all products, this compares the CTE counter with the number of returned orders.