Sql Recursive query to identify relation between 2 users of family tree

Question

Users Data

    UserId  DisplayName
12  Rahul
13  Anjali
14  Faruk
15  Shabina
16  Shakira
17  Ali
18  Ronak
19  Dali
20  Bali
21  Kali
22  Katrina
23  Sita
24  Gita
25  Ram
26  Shyam
27  Suhana
28  Suhas
29  Raj
30  Taslim
31  Ritik
32  Tejas
33  Dipika
34  Bush
35  Dyna
36  Bushiar
37  Salman
38  Ruksana
39  Khushi
40  Tazz
41  Miki
42  Krish
43  Kumbh

Family Tree Data

ID  UserID RelativeId Relation
1   12 13 Spouse
3   12 15 Daughter
4   12 16 Daughter
5   12 17 Son
6   12 18 Son
7   13 12 Spouse
9   13 15 Daughter
10  13 16 Daughter
11  13 17 Son
12  13 18 Son
13  14 15 Spouse
14  14 20 Son
15  14 21 Daughter
16  14 19 Daughter
17  15 12 Father
18  15 13 Mother
19  15 14 Spouse
20  15 16 Sister
21  15 18 Brother
22  15 20 Son
23  15 19 Daughter
24  15 21 Daughter
25  16 12 Father
26  16 13 Mother
27  16 17 Spouse
28  16 14 Brother
29  16 18 Brother
30  16 23 Daughter
31  16 24 Daughter
32  16 25 Son
33  16 26 Son
34  17 16 Spouse
35  17 23 Daughter
36  17 24 Daughter
37  17 25 Son
38  17 26 Son
39  18 12 Father
40  18 13 Mother
41  18 19 Spouse
42  18 27 Daughter
43  18 28 Son
44  18 29 Son
45  18 15 Sister
46  18 16 Sister
47  19 14 Father
48  19 15 Mother
49  19 18 Spouse
50  19 27 Daughter
51  19 28 Son
52  19 29 Son
53  19 20 Brother
54  19 21 Sister
55  20 14 Father
56  20 15 Mother
57  20 21 Sister
58  20 19 Sister
59  20 40 Spouse
60  20 42 Son
61  20 43 Son
62  21 14 Father
63  21 15 Mother
64  21 25 Spouse
65  21 40 Daughter
66  21 41 Son
67  21 20 Brother
68  21 19 Sister
102 22 28 Spouse
103 22 31 Son
69  23 16 Mother
70  23 17 Father
71  23 24 Sister
72  23 25 Brother
73  23 26 Brother
74  24 16 Mother
75  24 17 Father
76  24 23 Sister
77  24 25 Brother
78  24 26 Brother
79  25 16 Mother
80  25 17 Father
81  25 23 Sister
82  25 24 Sister
83  25 26 Brother
84  25 21 Spouse
85  25 40 Daughter
86  25 41 Son
87  26 16 Mother
88  26 17 Father
89  26 23 Sister
90  26 24 Sister
91  26 25 Brother
92  26 27 Spouse
93  26 34 Son
94  26 31 Daughter
95  27 18 Father
96  27 19 Mother
97  27 26 Spouse
98  27 28 Brother
99  27 29 Brother
100 27 34 Son
101 27 35 Daughter
104 28 18 Father
105 28 19 Mother
106 28 27 Sister
107 28 29 Brother
108 28 22 Spouse
109 28 31 Son
110 29 18 Father
111 29 19 Mother
112 29 27 Sister
113 29 28 Brother
114 29 30 Spouse
115 29 32 Son
116 30 29 Spouse
117 30 32 Son
118 31 28 Father
119 31 22 Mother
120 31 34 Spouse
121 31 36 Son
122 32 29 Father
123 32 30 Mother
124 32 33 Spouse
125 32 35 Spouse
126 32 37 Son
127 32 38 Daughter
128 33 32 Spouse
129 33 38 Daughter
130 34 26 Father
131 34 27 Mother
132 34 35 Sister
133 34 31 Spouse
134 34 36 Son
135 35 26 Father
136 35 27 Mother
137 35 32 Spouse
138 35 34 Brother
139 35 37 Son
140 36 34 Father
141 36 31 Father
142 37 35 Mother
143 37 32 Father
144 37 38 Spouse
145 37 39 Daughter
146 38 32 Father
147 38 33 Mother
148 38 37 Spouse
149 38 39 Daughter
150 39 37 Father
151 39 38 Mother
152 40 21 Mother
153 40 25 Father
154 40 41 Brother
155 40 20 Spouse
156 40 42 Son
157 40 43 Son
158 41 21 Mother
159 41 25 Father
160 41 40 Sister
161 42 20 Father
162 42 40 Mother
163 42 43 Brother
164 43 20 Father
165 43 40 Mother
166 43 42 Brother

want to find path to find relation between Rahul(Id=12) and Tejas(Id=32) thanks for the help in advance....

Chart for the above Data:

query provided by astander:

DECLARE @Users TABLE(
        UserID INT,
        UserName VARCHAR(50)
)

DECLARE @FamilyTree TABLE(
        UserID INT,
        RelationID INT,
        Relation VARCHAR(50)
)



INSERT INTO @Users (UserID,UserName) SELECT 1 ,'Rahul'
INSERT INTO @Users (UserID,UserName) SELECT 2 ,'Anjli'
INSERT INTO @Users (UserID,UserName) SELECT 3 ,'Ronak'
INSERT INTO @Users (UserID,UserName) SELECT 4 ,'Rutu'
INSERT INTO @Users (UserID,UserName) SELECT 5 ,'Riya'
INSERT INTO @Users (UserID,UserName) SELECT 6 ,'Rishi'
INSERT INTO @Users (UserID,UserName) SELECT 7 ,'Harry'
INSERT INTO @Users (UserID,UserName) SELECT 8 ,'Dyna'

INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 2 ,'Wife'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 1 ,'Rahul'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 3 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 4 ,'Wife'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 5 ,'Daughter'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 5 ,'Daughter'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 6 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 6 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 7 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 7 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 7, 8 ,'Wife'

DECLARE @UserID1 INT,
        @UserID2 INT

SELECT  @UserID1 = 1,
        @UserID2 = 6


DECLARE @Table TABLE(
        cnt INT,
        cpath VARCHAR(50)
)

;WITH fam AS(
        SELECT  *,
                        1 AS CntLevel,
                        CAST('/' + CAST(ft.UserID AS VARCHAR(10)) + '/' AS VARCHAR(50)) AS CLevel
        FROM    @FamilyTree ft 
        WHERE   ft.UserID = @UserID1
        UNION ALL
        SELECT  ft.*,
                        fam.CntLevel + 1 AS CntLevel,
                        CAST(fam.Clevel  + CAST(ft.UserID AS VARCHAR(10)) + '/'AS VARCHAR(50)) AS CLevel
        FROM    @FamilyTree ft INNER JOIN
                        fam ON ft.UserID = fam.RelationID
        WHERE   ft.UserID != @UserID1
)
INSERT INTO @Table(cnt, cpath)
SELECT  CntLevel,
        CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50))
FROM    fam
WHERE   CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('/' + CAST(@UserID1 AS VARCHAR(10)) + '/%')
AND     CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('%/' + CAST(@UserID2 AS VARCHAR(10))+ '/')

SELECT  DISTINCT 
        ft.* 
FROM    @Table t INNER JOIN
        @FamilyTree ft  ON      PATINDEX('%/' + CAST(ft.UserID AS VARCHAR(10)) + '/%',t.cpath) > 0
                                        AND PATINDEX('%/' + CAST(ft.RelationID AS VARCHAR(10)) + '/%',t.cpath) > 0

Answer 1

You can try something like this

DECLARE @Users TABLE(
     UserID INT,
     UserName VARCHAR(50)
)

DECLARE @FamilyTree TABLE(
     UserID INT,
     RelationID INT,
     Relation VARCHAR(50)
)



INSERT INTO @Users (UserID,UserName) SELECT 1 ,'Rahul'
INSERT INTO @Users (UserID,UserName) SELECT 2 ,'Anjli'
INSERT INTO @Users (UserID,UserName) SELECT 3 ,'Ronak'
INSERT INTO @Users (UserID,UserName) SELECT 4 ,'Rutu'
INSERT INTO @Users (UserID,UserName) SELECT 5 ,'Riya'
INSERT INTO @Users (UserID,UserName) SELECT 6 ,'Rishi'
INSERT INTO @Users (UserID,UserName) SELECT 7 ,'Harry'
INSERT INTO @Users (UserID,UserName) SELECT 8 ,'Dyna'

INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 2 ,'Wife'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 1 ,'Rahul'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 3 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 4 ,'Wife'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 5 ,'Daughter'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 5 ,'Daughter'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 6 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 6 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 7 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 7 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 7, 8 ,'Wife'

DECLARE @UserID1 INT,
     @UserID2 INT

SELECT  @UserID1 = 1,
     @UserID2 = 6


DECLARE @Table TABLE(
     cnt INT,
     cpath VARCHAR(50)
)

;WITH fam AS(
     SELECT *,
       1 AS CntLevel,
       CAST('/' + CAST(ft.UserID AS VARCHAR(10)) + '/' AS VARCHAR(50)) AS CLevel
     FROM @FamilyTree ft 
     WHERE ft.UserID = @UserID1
     UNION ALL
     SELECT ft.*,
       fam.CntLevel + 1 AS CntLevel,
       CAST(fam.Clevel  + CAST(ft.UserID AS VARCHAR(10)) + '/'AS VARCHAR(50)) AS CLevel
     FROM @FamilyTree ft INNER JOIN
       fam ON ft.UserID = fam.RelationID
     WHERE ft.UserID != @UserID1
)
INSERT INTO @Table(cnt, cpath)
SELECT  CntLevel,
     CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50))
FROM    fam
WHERE   CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('/' + CAST(@UserID1 AS VARCHAR(10)) + '/%')
AND  CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('%/' + CAST(@UserID2 AS VARCHAR(10))+ '/')

SELECT  DISTINCT 
     ft.* 
FROM    @Table t INNER JOIN
     @FamilyTree ft ON PATINDEX('%/' + CAST(ft.UserID AS VARCHAR(10)) + '/%',t.cpath) > 0
         AND PATINDEX('%/' + CAST(ft.RelationID AS VARCHAR(10)) + '/%',t.cpath) > 0

Answer 2

i found one good article from there i am able to make my query link for the article

Query :

DECLARE @From_Node int,

        @To_Node   int

SET @From_Node = 100035

SET @To_Node   = 100046



SELECT *,

       edges = 5 - ( CASE WHEN p2 = -1 THEN 4 WHEN p3 = -1 THEN 3 WHEN p4 = -1 THEN 2 WHEN p5 = -1 THEN 1 ELSE 0 END )

FROM (

    SELECT p0 = p1.From_Node,

           p1 = COALESCE( p1.To_Node, -1 ),

           p2 = COALESCE( p2.To_Node, -1 ),

           p3 = COALESCE( p3.To_Node, -1 ),

           p4 = COALESCE( p4.To_Node, -1 ),

           p5 = COALESCE( p5.To_Node, -1 )

    FROM paths AS p1

        OUTER APPLY tvfn_get_nodes_for( p1.To_Node, @From_Node, @To_Node ) AS p2

        OUTER APPLY tvfn_get_nodes_for( p2.To_Node, @From_Node, @To_Node ) AS p3

        OUTER APPLY tvfn_get_nodes_for( p3.To_Node, @From_Node, @To_Node ) AS p4

        OUTER APPLY tvfn_get_nodes_for( p4.To_Node, @From_Node, @To_Node ) AS p5

    WHERE p1.From_Node = @From_Node

    ) AS d

WHERE ( p1 = @To_Node OR p2 = @To_Node OR p3 = @To_Node OR p4 = @To_Node OR p5 = @To_Node )

  AND ( p1 NOT IN ( p2, p3, p4, p5 )

     OR p1 = -1 )

  AND ( p2 NOT IN ( p1, p3, p4, p5 )

     OR p2 = -1 )

  AND ( p3 NOT IN ( p1, p2, p4, p5 )

     OR p3 = -1 )

  AND ( p4 NOT IN ( p1, p2, p3, p5 )

     OR p4 = -1 )

  AND ( p5 NOT IN ( p1, p2, p3, p4 )

     OR p5 = -1 )

ORDER BY edges

Answer 3

Well, complicated family. Try this. Work with finally output for customize.

DECLARE @Users TABLE(
        UserID INT,
        UserName VARCHAR(50)
)

DECLARE @FamilyTree TABLE( [ID] int, 
        UserID INT,
         RelativeId INT,
        Relation VARCHAR(50)
)



INSERT INTO @Users
select  12,'Rahul' union
select  13,'Anjali' union
select  14,'Faruk' union
select  15,'Shabina' union
select  16,'Shakira' union
select  17,'Ali' union
select  18,'Ronak' union
select  19,'Dali' union
select  20,'Bali' union
select  21,'Kali' union
select  22,'Katrina' union
select  23,'Sita' union
select  24,'Gita' union
select  25,'Ram' union
select  26,'Shyam' union
select  27,'Suhana' union
select  28,'Suhas' union
select  29,'Raj' union
select  30,'Taslim' union
select  31,'Ritik' union
select  32,'Tejas' union
select  33,'Dipika' union
select  34,'Bush' union
select  35,'Dyna' union
select  36,'Bushiar' union
select  37,'Salman' union
select  38,'Ruksana' union
select  39,'Khushi' union
select  40,'Tazz' union
select  41,'Miki' union
select  42,'Krish' union
select  43,'Kumbh' 




INSERT INTO @FamilyTree ([ID],UserID,RelativeId,Relation)
select  1,12,13,'Spouse' union
select  3,12,15,'Daughter' union
select  4,12,16,'Daughter' union
select  5,12,17,'Son' union
select  6,12,18,'Son' union
select  7,13,12,'Spouse' union
select  9,13,15,'Daughter' union
select  10,13,16,'Daughter' union
select  11,13,17,'Son' union
select  12,13,18,'Son' union
select  13,14,15,'Spouse' union
select  14,14,20,'Son' union
select  15,14,21,'Daughter' union
select  16,14,19,'Daughter' union
select  17,15,12,'Father' union
select  18,15,13,'Mother' union
select  19,15,14,'Spouse' union
select  20,15,16,'Sister' union
select  21,15,18,'Brother' union
select  22,15,20,'Son' union
select  23,15,19,'Daughter' union
select  24,15,21,'Daughter' union
select  25,16,12,'Father' union
select  26,16,13,'Mother' union
select  27,16,17,'Spouse' union
select  28,16,14,'Brother' union
select  29,16,18,'Brother' union
select  30,16,23,'Daughter' union
select  31,16,24,'Daughter' union
select  32,16,25,'Son' union
select  33,16,26,'Son' union
select  34,17,16,'Spouse' union
select  35,17,23,'Daughter' union
select  36,17,24,'Daughter' union
select  37,17,25,'Son' union
select  38,17,26,'Son' union
select  39,18,12,'Father' union
select  40,18,13,'Mother' union
select  41,18,19,'Spouse' union
select  42,18,27,'Daughter' union
select  43,18,28,'Son' union
select  44,18,29,'Son' union
select  45,18,15,'Sister' union
select  46,18,16,'Sister' union
select  47,19,14,'Father' union
select  48,19,15,'Mother' union
select  49,19,18,'Spouse' union
select  50,19,27,'Daughter' union
select  51,19,28,'Son' union
select  52,19,29,'Son' union
select  53,19,20,'Brother' union
select  54,19,21,'Sister' union
select  55,20,14,'Father' union
select  56,20,15,'Mother' union
select  57,20,21,'Sister' union
select  58,20,19,'Sister' union
select  59,20,40,'Spouse' union
select  60,20,42,'Son' union
select  61,20,43,'Son' union
select  62,21,14,'Father' union
select  63,21,15,'Mother' union
select  64,21,25,'Spouse' union
select  65,21,40,'Daughter' union
select  66,21,41,'Son' union
select  67,21,20,'Brother' union
select  68,21,19,'Sister' union
select  102,22,28,'Spouse' union
select  103,22,31,'Son' union
select  69,23,16,'Mother' union
select  70,23,17,'Father' union
select  71,23,24,'Sister' union
select  72,23,25,'Brother' union
select  73,23,26,'Brother' union
select  74,24,16,'Mother' union
select  75,24,17,'Father' union
select  76,24,23,'Sister' union
select  77,24,25,'Brother' union
select  78,24,26,'Brother' union
select  79,25,16,'Mother' union
select  80,25,17,'Father' union
select  81,25,23,'Sister' union
select  82,25,24,'Sister' union
select  83,25,26,'Brother' union
select  84,25,21,'Spouse' union
select  85,25,40,'Daughter' union
select  86,25,41,'Son' union
select  87,26,16,'Mother' union
select  88,26,17,'Father' union
select  89,26,23,'Sister' union
select  90,26,24,'Sister' union
select  91,26,25,'Brother' union
select  92,26,27,'Spouse' union
select  93,26,34,'Son' union
select  94,26,31,'Daughter' union
select  95,27,18,'Father' union
select  96,27,19,'Mother' union
select  97,27,26,'Spouse' union
select  98,27,28,'Brother' union
select  99,27,29,'Brother' union
select  100,27,34,'Son' union
select  101,27,35,'Daughter' union
select  104,28,18,'Father' union
select  105,28,19,'Mother' union
select  106,28,27,'Sister' union
select  107,28,29,'Brother' union
select  108,28,22,'Spouse' union
select  109,28,31,'Son' union
select  110,29,18,'Father' union
select  111,29,19,'Mother' union
select  112,29,27,'Sister' union
select  113,29,28,'Brother' union
select  114,29,30,'Spouse' union
select  115,29,32,'Son' union
select  116,30,29,'Spouse' union
select  117,30,32,'Son' union
select  118,31,28,'Father' union
select  119,31,22,'Mother' union
select  120,31,34,'Spouse' union
select  121,31,36,'Son' union
select  122,32,29,'Father' union
select  123,32,30,'Mother' union
select  124,32,33,'Spouse' union
select  125,32,35,'Spouse' union
select  126,32,37,'Son' union
select  127,32,38,'Daughter' union
select  128,33,32,'Spouse' union
select  129,33,38,'Daughter' union
select  130,34,26,'Father' union
select  131,34,27,'Mother' union
select  132,34,35,'Sister' union
select  133,34,31,'Spouse' union
select  134,34,36,'Son' union
select  135,35,26,'Father' union
select  136,35,27,'Mother' union
select  137,35,32,'Spouse' union
select  138,35,34,'Brother' union
select  139,35,37,'Son' union
select  140,36,34,'Father' union
select  141,36,31,'Father' union
select  142,37,35,'Mother' union
select  143,37,32,'Father' union
select  144,37,38,'Spouse' union
select  145,37,39,'Daughter' union
select  146,38,32,'Father' union
select  147,38,33,'Mother' union
select  148,38,37,'Spouse' union
select  149,38,39,'Daughter' union
select  150,39,37,'Father' union
select  151,39,38,'Mother' union
select  152,40,21,'Mother' union
select  153,40,25,'Father' union
select  154,40,41,'Brother' union
select  155,40,20,'Spouse' union
select  156,40,42,'Son' union
select  157,40,43,'Son' union
select  158,41,21,'Mother' union
select  159,41,25,'Father' union
select  160,41,40,'Sister' union
select  161,42,20,'Father' union
select  162,42,40,'Mother' union
select  163,42,43,'Brother' union
select  164,43,20,'Father' union
select  165,43,40,'Mother' union
select  166,43,42,'Brother'  




DECLARE @UserID1 INT,
        @UserID2 INT

SELECT  @UserID1 = 12,
        @UserID2 = 32

--descendants of user1
;with famDes
as
(
    select * , 
     cast(ltrim(userid)  as varchar(max)) as [path],
     cast(relation  as varchar(max)) as [path2]
    from @FamilyTree
    where UserID=@UserID1 and Relation in ('Son','Daughter')

    union all
    select ft.*, 
     cast([path]+'.'+ltrim(ft.userid) as varchar(max)),
     cast([path2]+'.'+ft.relation  as varchar(max))
    from @FamilyTree ft
    inner join famDes on ft.UserID=famDes.RelativeId and ft.Relation in ('Son','Daughter')

),
--ascendants of user1
famAsc
as
(
    select * , 
     cast(ltrim(userid)  as varchar(max)) as [path],
     cast(relation  as varchar(max)) as [path2]
    from @FamilyTree
    where UserID=@UserID1 and Relation in ('Father','Mother')

    union all
    select ft.*, 
     cast([path]+'.'+ltrim(ft.userid) as varchar(max)),
     cast([path2]+'.'+ft.relation  as varchar(max))
    from @FamilyTree ft
    inner join famAsc on ft.UserID=famAsc.RelativeId and ft.Relation in ('Father','Mother')

)
select 
    * 
from 
    famDes
where  
    relativeID=@UserID2 
union all
select 
    * 
from 
    famAsc
where  
    relativeID=@UserID2 
union all
--if user1 and user2 are brothers or Spouses
select 
    * , 
    cast(ltrim(userid)  as varchar(max)) as [path],
    cast(relation  as varchar(max)) as [path2]
    from @FamilyTree
where 
    UserID in (@UserID1,@UserID2) 
    and Relation in ('Brother','Sister','Spouse')
    and relativeID in (@UserID2 ,@UserID1 )