declare @tree table (parent int, child int )
insert into @tree values(1,2)
insert into @tree values(1,8)
insert into @tree values(1,4)
insert into @tree values(1,6)
insert into @tree values(1,7)
insert into @tree values(2,5)
insert into @tree values(8,5)
insert into @tree values(4,5)
insert into @tree values(5,3)
insert into @tree values(6,3)
insert into @tree values(7,9)
insert into @tree values(3,10)
insert into @tree values(9,10)
declare @elem int, @level int
select @elem=5, @level=0 -- @elem - элемент, с которого начинаем поиск подузлов
declare @wave table (parent int null, child int null, level int)
insert into @wave
select *, @level
from @tree
where parent=@elem
while @@rowcount>0
begin
select @level=@level+1
insert into @wave
select t.child, coalesce(t.parent,w.parent), @level
from @wave w
left join @tree t on w.parent=t.parent
where w.level=@level-1 and w.parent is not null
end
select distinct child from @wave
http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=207456&msg=1782565