StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This sort of thing is very difficult to do in SQL, as SQL is designed to generate its result set by, at the most basic level, comparing a set of column values on a single row each to another value. What you're trying to do is compare a single column value (or set of column values) on <em>multiple rows</em> to another set of <em>multiple rows</em>.</p> <p>In order to do this, you'll have to create some kind of order signature. Strictly speaking, this isn't possible to do using query syntax alone; you'll have to use some T-SQL.</p> <pre><code>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 </code></pre> <p>This assumes (based on the wording of your question) that ordering multiple of the same item in an order will result in multiple rows, rather than using a <code>Quantity</code> column. If the latter is the case, just remove the <code>group by</code> from the <code>@Items</code> population query and replace <code>Count(ItemID)</code> with <code>Quantity</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload