Finding Completely Overlapping Segments

ok folks, I need help.

Here's the table and some sample data:


declare @t table
(
segment int,
subsegment int,
primary key (segment,subsegment)
)
insert @t
select 1,33 union all
select 1,22 union all
select 2,33 union all
select 2,22 union all
select 3,33 union all
select 3,22 union all
select 3,44


What I want is to find all segments that are in some sense complete duplicates of other segments. a segment is made up of subsegments. a subsegment is not a segment - it's a completely different entity. this table is not hierarchical.

So in the sample data above, segments 1 and 2 are dupes because they share exactly the same subsegments: 22 and 33. Segment 3 is not a dupe because it has a third subsegment the other two don't have: 44.

when a duped segment is found, I need to know which other segment it duplicates. so an acceptable result set for the above sample data would be:


segment partner
------- -------
1 2


this would also be fine:


segment partner
------- -------
1 2
2 1


ps: i already posted this on dbforums - just broadening the audience a little.

elsasoft.org