How to retrieve a cluster of connected users from table of connections between two users?

Question

Hi,

The table consists of pairs of users which are connected together. The following is a hypothetical example:

user1, user2
a, b
a, c
c, a
c, d
d, e
a, e
j, n
g, n
f, n

By randomly picking up a user from the table (user1 or user2) I would like to retrieve the whole cluster of connections to which the selected user belongs. For example if the user d is selected the query (or an algorithm) should return connections

a, b
a, c
c, a
c, d
d, e
a, e

Does anyone know how to form a query statement or create an algorithm to retrieve the connections cluster?

Thank you!

Answer 1

Modeling trees and more general graphs in SQL is tricky, but can be done.

You may google for the "part explosions SQL" keywords, and you'll find a lot of references.

You may find a way to model a very similar problem in MySql here.

Answer 2

By using a recursive CTE, like so:

with combinedusers as 
(select user1 userX, user2 userY from usertable union
 select user2 userX, user1 userY from usertable)
, relatedusers as 
(select c.userX, 
        c.userY, 
        convert(varchar(max),'\' + c.userX + '\' + c.userY + '\') hierarchy 
        from combinedusers c where userX = 'd' 
 union all
 select c.userX, 
        c.userY, 
        convert(varchar(max),r.hierarchy  + c.userY + '\') hierarchy 
        from combinedusers c 
        join relatedusers r 
        on c.userX = r.userY and charindex('\' + c.userY + '\',r.hierarchy)=0)
select * from
(select userX, userY from relatedusers union 
 select userY, userX from relatedusers) r where userX < userY