title: Sql Recursive query to identify relation between 2 users of family tree
body: <p>Users Data</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Family Tree Data</p>
  
  <pre><code>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
  </code></pre>
  
  <p>want to find path to find relation between Rahul(Id=12) and Tejas(Id=32)
  thanks for the help in advance....</p>
  
  <p><strong>Chart for the above Data:</strong>
  <a href="https://i.stack.imgur.com/Co41T.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Co41T.jpg" alt="alt text"></a>
  </p>
  
  <p>query provided by astander:</p>
  
  <pre><code>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) &gt; 0
                                          AND PATINDEX('%/' + CAST(ft.RelationID AS VARCHAR(10)) + '/%',t.cpath) &gt; 0
  </code></pre>