T-SQL Find Pairs Where at Least one Member has Two or More Pairs with Different Members

Viewed 53

I have a dataset with two columns:

source          target
Michael Scott   Kelly Kapoor
Jim Halpert     Pam Beasley
Jim Halpert     Pam Beasley
Dwight Schrute  Angela
Angela          Dwight Schrute
Erin            Meredith
Erin            Meredith
Kevin Malone    Stanley Hudson
Kevin Malone    Ryan Howard
Pam Beasley     Oscar

I want to find the rows that include at least one member who has multiple pairs with at least two different members. So, the end result should return:

source          target          
Jim Halpert     Pam Beasley
Jim Halpert     Pam Beasley
Kevin Malone    Stanley Hudson
Kevin Malone    Ryan Howard
Pam Beasley     Oscar

Michael --> Kelly is removed because neither has any other links Dwight Schrute --> Angela and Angela --> Dwight Schrute are removed because, although there are multiple links, the links are between the same members. Erin --> Meredith and Erin --> Meredith are removed because, again, the links are between the same members (although in the same direction).

I know how to find distinct links that involve the same members in either direction:

select source
      ,target
from dbo.networktest
group by source, target
having count(*) > 1
union
select b.source
      ,b.target
from dbo.networktest a
left outer join dbo.networktest b on a.source = b.target and a.target = b.source
where b.source is not null and b.target is not null

How would I change (or scrap/rebuild) that to accomplish my objective? Thank you for any insight you all might have! If I can make my question more clear, please let me know.

1 Answers
Related