Knowledgebase

If you have a table containing hierarchical data (e.g. a parent having children) and you want to show all the children and grand children for a person you can use a "Common Table Expression" in Microsoft SQL Server 2008.

Table Person

Table Person Data

truncate table person
insert into person (Id, Name, Birthday, ParentId) values (1, 'John Do', '1950-6-1', null)
insert into person (Id, Name, Birthday, ParentId) values (2, 'Carl Do', '1980-3-5', 1)
insert into person (Id, Name, Birthday, ParentId) values (3, 'Michael Do', '2010-12-12', 2)
insert into person (Id, Name, Birthday, ParentId) values (4, 'Tonny Jackson', '1915-3-3', null)
insert into person (Id, Name, Birthday, ParentId) values (5, 'Bo Jackson', '1945-7-7', 4)
insert into person (Id, Name, Birthday, ParentId) values (6, 'Harry Jackson', '1981-4-4', 5)
insert into person (Id, Name, Birthday, ParentId) values (7, 'Bart Jackson', '2009-12-11', 6)

Returning a person with all it’s children and grandchildren

declare @PersonId int
set @PersonId = 4

-- Define the "Common Table Expression" with name "person_cte"
; with person_cte as (
 
 -- Select all the direct children of the given person
 select        p2.Id,
             p2.Name,
            p2.Birthday,
            p2.ParentId
 from        person p1
 inner join person p2 on p2.ParentId = p1.Id
 where        p1.Id = @PersonId 

 union all

 -- Add grandchildren's, by recursively calling this "Common Table Expression"
 select        p1.Id,
            p1.Name,
            p1.Birthday,
            p1.ParentId
 from        person_cte p2
 inner join person p1 on p1.ParentId = p2.Id
 
) 
select
    Id,
    Name,
    Birthday,
    ParentId
from person_cte

union all

-- Add the given person to the result
select
    Id,
    Name,
    Birthday,
    ParentId
from person
where Id = @PersonId
order by Birthday

Result

Background information:

Using Common Table Expressions: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Recursive Queries Using Common Table Expressions: http://msdn.microsoft.com/en-us/library/ms186243.aspx