Genealogy Tree in SQL Server

Question

In my table each record has a pointer to his direct parent and I need to know how to query for all the descendants, direct or not (the son of a son), of a given record.

Facts:

I don't know how deep the tree is and i need all the leafs
Some records might not have a child record
I'm running under SqlServer 2012

Can anyone tell me how to write such a query?

This is not too hard using CTEs. See http://stackoverflow.com/questions/3916597/sql-server-recursive-query?rq=1 or http://stackoverflow.com/questions/6954779/sql-multi-condition-cte-recursion as examples — hatchet - done with SOverflow, Jan 10 '13 at 18:13
http://msdn.microsoft.com/en-us/library/ms186243(v=SQL.105).aspx Just search msdn for tsql recursion — paparazzo, Jan 10 '13 at 18:17
If you posted your table structure, sample data and the desired result, then you will probably get help writing it. — Taryn, Jan 10 '13 at 19:18
@hatchet could you pls write that as official answer so I can accept it as a official answer? — Leonardo, Jan 11 '13 at 11:56

score 4 · Accepted Answer · answered Jan 16 '13 at 16:14

Building on the query from Josh Wallace, to get descendents.

declare @people table
(
    Id int,
    Name nvarchar(50),
    ParentId int
)

insert into @people values (1, 'Frank', null)
insert into @people values (2, 'Shane', null)
insert into @people values (3, 'George', 1)
insert into @people values (5, 'Hank', 1)
insert into @people values (7, 'Abigail', 3)
insert into @people values (8, 'Ben', 3)
insert into @people values (9, 'Charlie', 5)
insert into @people values (10, 'Dave', 5)
insert into @people values (11, 'Homer', 5)
insert into @people values (12, 'Junebug', 7)
insert into @people values (13, 'Bart', 11)
insert into @people values (14, 'Lisa', 11)


;with descendents AS (
    -- Anchor member definition
    select p.Id, p.Name, p.ParentId, Level = 0 
    from @people p 
    where p.Id = 1 -- person to search for descendents 

    union all

    -- Recursive member definition
    select p.Id, p.Name, p.ParentId, Level + 1
    from @people p 
    inner join descendents a on p.ParentId = a.Id
)


select 
    a.Level, ParentID = p.Id, Parent = p.Name, a.Id, Child = a.Name
from descendents a 
    left join @people p on p.Id = a.ParentId
order by a.Level

jaybeeuu · Answer 2 · 2013-01-17T12:28:13.123

Using a CTE for recurrsion seems like the simplest route to me, here's an example...

DECLARE @people TABLE
(
    Id INT,
    Name NVARCHAR(50),
    ParentId INT
)

INSERT INTO @people (Id, Name, ParentId) VALUES (1, 'Abigail', null)
INSERT INTO @people (Id, Name, ParentId) VALUES (2, 'Ben', 5)
INSERT INTO @people (Id, Name, ParentId) VALUES (3, 'Charlie', 2)
INSERT INTO @people (Id, Name, ParentId) VALUES (4, 'Dave', 1)
INSERT INTO @people (Id, Name, ParentId) VALUES (5, 'Ed', 6)
INSERT INTO @people (Id, Name, ParentId) VALUES (6, 'Frank', null)

;WITH ancestors AS (
 SELECT Id, Name, ParentId FROM @people WHERE Id = 3 
 UNION ALL
 SELECT p.Id, p.Name, p.parentId FROM @people p
 INNER JOIN ancestors a ON a.parentId = p.Id --This inner join causes the recurrsion
)
SELECT Id, Name, ParentId FROM ancestors

To get from the children to the ancestors switch the ON statement to be the other way arround:

DECLARE @people TABLE
(
    Id INT,
    Name NVARCHAR(50),
    ParentId INT
)

INSERT INTO @people (Id, Name, ParentId) VALUES (1, 'Abigail', null)
INSERT INTO @people (Id, Name, ParentId) VALUES (2, 'Ben', 5)
INSERT INTO @people (Id, Name, ParentId) VALUES (3, 'Charlie', 2)
INSERT INTO @people (Id, Name, ParentId) VALUES (4, 'Dave', 1)
INSERT INTO @people (Id, Name, ParentId) VALUES (5, 'Ed', 6)
INSERT INTO @people (Id, Name, ParentId) VALUES (6, 'Frank', null)

;WITH ancestors AS (
 SELECT Id, Name, ParentId FROM @people WHERE Id = 6
 UNION ALL
 SELECT p.Id, p.Name, p.parentId FROM @people p
 --INNER JOIN ancestors a ON a.parentId = p.Id --It was this to go down...
   INNER JOIN ancestors a ON a.Id = p.parentId --Now use this to go up the tree
)
SELECT Id, Name, ParentId FROM ancestors

Vote up but not quite what I want... you see if query for the descendants of Frank (6) i get only Frank... I endeed can get to Frank from any of his descendants but i need the other way around: From father to son... — Leonardo, Jan 16 '13 at 11:20

Genealogy Tree in SQL Server

2 Answers2