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.