Databases
StackOverflow2013
Postsdbo
POSql Recursive query to identify relation between 2 users of family tree
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql Recursive query to identify relation between 2 users of family tree
    primarykey
    data
    text
    <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="http://i46.tinypic.com/vyl1fd.jpg" rel="nofollow noreferrer">alt text http://i46.tinypic.com/vyl1fd.jpg</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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRadhi
    1. USRadhi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you using Oracle? Please post your attempted query that didn't work.
      singulars
      1. POSql Recursive query to identify relation between 2 users of family tree
      1. USAsaph
    2. CO@Asaph: There ain't no ORA error - that's a SQL Server error
      singulars
      1. POSql Recursive query to identify relation between 2 users of family tree
      1. USOMG Ponies
    3. COi am using sql server 2008 Query: declare @U1 bigint declare @U2 bigint set @U1=1 set @U2=6 declare @Tbl table (UserID bigint,RelativeId bigint,RelationId bigint,PLevel int); WITH abcd AS ( SELECT UserID,RelativeId,RelationId,1 as PLevel FROM FamilyTree t1 WHERE UserId = @U1 UNION ALL SELECT a.RelativeId as UserID,t2.RelativeId,t2.RelationId,a.PLevel+1 as PLevel FROM FamilyTree t2 JOIN abcd AS a ON t2.UserID = a.RelativeId where t2.Relativeid!=@U1 and t2.USerId!=a.UserId and t2.UserId!=@U2 ) SELECT * FROM abcd
      singulars
      1. POSql Recursive query to identify relation between 2 users of family tree
      1. USRadhi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload