t, Sql Server - rextester

Language: Layout:

CREATE TABLE [Relations](
    [FromID] [int] NULL,
    [ToID] [int] NULL
) ;
   
INSERT [Relations] ([FromID], [ToID]) VALUES (1, 2);
INSERT [Relations] ([FromID], [ToID]) VALUES (2, 3);
INSERT [Relations] ([FromID], [ToID]) VALUES (2, 4);
INSERT [Relations] ([FromID], [ToID]) VALUES (5, 6);
INSERT [Relations] ([FromID], [ToID]) VALUES (6, 7);
INSERT [Relations] ([FromID], [ToID]) VALUES (8, 9);
        
SELECT *
FROM [Relations] R1
LEFT JOIN [Relations] R2
  ON R1.[FromID] = R2.[ToID];
  
WITH husbands as (      
    SELECT  ROW_NUMBER() OVER (ORDER BY R1.[FromID]) as [familyID],
            R1.[FromID] as [ID]           
    FROM [Relations] R1
    LEFT JOIN [Relations] R2
      ON R1.[FromID] = R2.[ToID]
    WHERE R2.[FromID] IS NULL
), wifes as (
    SELECT H.[familyID], R1.[ToID] as [ID]
    FROM husbands H
    JOIN Relations R1
      ON H.[ID] = R1.[FromID] 
), childrens as (
    SELECT H.[familyID], R2.[ToID] as [ID]
    FROM husbands H
    JOIN Relations R1
      ON H.[ID] = R1.[FromID] 
    JOIN Relations R2
      ON R1.[ToID] = R2.[FromID]     
)    
SELECT * FROM husbands UNION 
SELECT * FROM wifes UNION 
SELECT * FROM childrens

View schema
Execution time: 0,03 sec, rows selected: 15, rows affected: 6, absolute service time: 0,2 sec