Find isolated group in the linked data

Question

I have a table of friends. Two columns: friend1 and friend2 containing ids of the friends with possible cycles The following data

(1,2) 
(2,1) 
(1,3) 
(4,5) 
(5,4)

means that 1,2,3 is one group and 4 and 5 are another group

I need to find all groups and list their members. I have tried with recursive CTE, but the cyclicity of the graph is a problem. Any idea?

Could you identify the cyclic ones beforehand and exclude one of them? — Nick.Mc, Mar 29 '21 at 12:56
[This](https://stackoverflow.com/a/42139978/92546) answer demonstrates a way to terminate recursion when there are cycles in the data by tracking the path for each row. — HABO, Mar 29 '21 at 14:06

score 1 · Accepted Answer · answered Mar 29 '21 at 13:12

You can use a recursive cte, but I guess you will have to break the cyclicity. Following a rough approach to it - on first glance it looked as expected, but you will have to modify it e.g. if you want to pivot the friends per group or whatever:

DECLARE @t TABLE(ID int, FriendID int)

INSERT INTO @t VALUES (1,2), (2,1), (1,3), (2,3), (4,5), (5,4)

;WITH cteDist AS(
SELECT DISTINCT CASE WHEN ID > FriendID THEN FriendID ELSE ID END AS ID,  CASE WHEN ID > FriendID THEN ID ELSE FriendID END AS FriendID
  FROM @t
),
cteTop AS(
SELECT DISTINCT i.ID
  FROM cteDist i
  LEFT JOIN cteDist f ON f.FriendID = i.ID
  WHERE f.ID IS NULL
),
cteGroups AS(
SELECT ID, ID AS FriendID
  FROM cteTop
UNION ALL
SELECT c.ID, f.FriendID
  FROM cteGroups c
  INNER JOIN cteDist f ON f.ID = c.FriendID
)
SELECT DISTINCT ID, FriendID
  FROM cteGroups
  ORDER BY 1, 2
  OPTION (MAXRECURSION 0)

Find isolated group in the linked data

1 Answers1