July 13, 2012 at 2:04 pm
I have a table of ItemID's associated with OrderID's and I would like to query the table to find all places where OrderID's share a common ItemID (I'll call it an overlap). I have been able to design a query to do this, but that solution shows the OrderID and it's overlapping OrderID twice (where one result row is the reflection of another result row).
Here is a simple illustration of the data:
[font="Courier New"]
create table OrderItem (
OrderID int,
ItemID int,
constraint OrderItem_PK primary key (OrderID, ItemID)
)
insert into OrderItem
(OrderID, ItemID)
values
(1, 100),
(2, 100),
(3, 101),
(10, 210),
(20, 210),
(30, 210),
(40, 211)
[/font]
Here is the desired result set:
OrderID Overlap_OrderID ItemID
-------- ------------------ --------
1 2 100
10 20 210
10 30 210
20 30 210
My solution also includes these 'reflections' in the result set:
OrderID Overlap_OrderID ItemID
-------- ------------------ --------
2 1 100
20 10 210
30 10 210
30 20 210
Thank you for any help you might be able to offer.
July 13, 2012 at 2:34 pm
can you post the query you are using to get the "Bad" result set
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 13, 2012 at 2:39 pm
You could just pick the ones where the second order id is greater than the first.
select *
from OrderItem o1
join OrderItem o2 on o2.itemid = o1.itemid and o2.orderid > o1.orderid
July 13, 2012 at 2:44 pm
i think this is your original query
SELECT o.OrderID, o2.OrderID, o.ItemID
FROM OrderItem o
INNER JOIN OrderItem o2
ON o.ItemID = o2.ItemID
AND o.OrderID <> o2.OrderID
and changing it to this is how to fix it.
SELECT o.OrderID, o2.OrderID, o.ItemID
FROM OrderItem o
INNER JOIN OrderItem o2
ON o.ItemID = o2.ItemID
AND o.OrderID < o2.OrderID
EDIT: Beaten to it
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 15, 2012 at 5:31 pm
Thank you both for your help with this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply