StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
1781528
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2009-11-23T07:07:26.020
FavoriteCount
0
LastActivityDate
2009-11-23T07:07:26.020
LastEditDate
LastEditorUserId
0
OwnerUserId
144424
ParentId
1781402
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
<p>You can try something like this</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) > 0 AND PATINDEX('%/' + CAST(ft.RelationID AS VARCHAR(10)) + '/%',t.cpath) > 0 </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POSql Recursive query to identify relation between 2 users of family tree
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USAdriaan Stander
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. COhi astander thanks for the help. but can you please check you query with my updated data in above question. i also provided on chart of family tree and highlighted the path i want to find from database.
  singulars
  PostPostId
  PO
  UserUserId
  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.