SQL Server Performance Forum – Threads Archive

SELECT from the tree

Hi,
How could I find all the children of the particular ancestor with the SQL SELECT from the table where the family tree stored:
CREATE TABLE family (id int NOT NULL PRIMARY KEY, id_ancestor int NOT NULL, first_name varchar (80), second name varchar(80))? Thanks.

---

– recursive CTE’s may help out. declare @t table (ColA int, ColB varchar(1), ColC int) insert @t
select 1, ‘A’, NULL
union all select 2, ‘B’, NULL
union all select 3, ‘C’, 7
union all select 4, ‘D’, NULL
union all select 5, ‘E’, 6
union all select 6, ‘F’, NULL
union all select 7, ‘G’, 9
union all select 8, ‘H’, NULL
union all select 9, ‘J’, NULL declare @Root int
set @Root = 3 declare @u table (level int, ColA int, ColB varchar(1), ColC int)
insert into @u select 1, ColA, ColB, ColC
from @t
where ColA = @Root declare @level int
set @level = 2
while not exists (select * from @u where ColC is null)
begin
insert into @u
select @level, ColA, ColB, ColC from @t
where ColA in (select ColC from @u where level = @level – 1)
set @level = @level + 1
end select @Root as ColA, ColB
from @u
order by level select *
from @u
order by level — SQL2005 using CTE ;with MyCTE
as (
select @level as MyLevel, ColA, ColB, ColC
from @t
where ColA = @Root
UNION ALL
select @level, A.ColA, A.ColB, A.ColC
from @t A
inner JOIN MyCTE B
on A.ColA = B.ColC
)
select @Root as ColA, ColB
from MyCTE go

---

other approach
http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html
Madhivanan Failing to plan is Planning to fail

---

]]>