Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Well, complicated family. Try this. Work with finally output for customize.</p> <pre> 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 ) </pre>
    singulars
    1. This table or related slice is empty.
    1. POSql Recursive query to identify relation between 2 users of family tree
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USguille
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSql Recursive query to identify relation between 2 users of family tree
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks guille. my next step today is to make query to find relations. that i got from your query. you saved my time. thanks again.
      singulars
      1. PO
      1. USRadhi
    2. COcan you help me to identify borther.son.daughter.husband kind of relations also...?
      singulars
      1. PO
      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