CODEWITHSUNDEEP
sql-servert-sqlrecursionrecursive-query
Leonardo
Leonardo

Reputation: 11387

Genealogy Tree in SQL Server

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:

Can anyone tell me how to write such a query?

Upvotes: 3

Views: 3234

Answers (2)

jaybeeuu
jaybeeuu

Reputation: 1123

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

Upvotes: 1

jim31415
jim31415

Reputation: 8808

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

Upvotes: 4

Related Questions