Sql Recursive query to identify relation between 2

问题:

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

回答1:

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 )

回答2:

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

回答3:

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