T-SQL Find pairs where at least one member has two or more pairs with different members
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 search for rows that contain at least one member that 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 it has no other links
Dwight Schrute --> Angela
and is Angela --> Dwight Schrute
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 (albeit in the same direction).
I know how to find different links that include the same elements in any 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 can I modify (or repair / rebuild) to accomplish my task? Thanks for any insight you all might have! If I can make my question clearer please let me know.
source to share
I think it exists
does what you need:
select nt.*
from networktest nt
where exists (select 1
from networktest nt2
where nt2.source in (nt.source, nt.target) and
nt2.target not in (nt.source, nt.target)
) or
exists (select 1
from networktest nt2
where nt2.target in (nt.source, nt.target) and
nt2.source not in (nt.source, nt.target)
);
source to share