Jeremy Stein - Brain
| « Good discussion of SQL Server temp tables | Why do I hear a beep whenever I wash my hands in the bathroom? » |
SQL Server recursive join
SQL Server 2000 does not support recursive joins. Oracle’s PL/SQL has the “connect by” mechanism, but T-SQL has nothing. So, here’s a way to get all the IDs of the descendants into a table variable. Assume a table MyTable has id and parent_id. Modify to meet your needs:
declare @descendant table
(id int, depth int)
declare @depth int
declare @cnt int
set @depth = 0
insert into @descendant
select id, @depth
from MyTable
where id = @initial_id --set this above
set @cnt = @@rowcount
while @cnt > 0
begin
set @depth = @depth + 1
insert into @descendant
select MyTable.id, @depth
from MyTable
inner join @descendant descendant
on descendant.id = MyTable.parent_id
where descendant.depth = @depth - 1
set @cnt = @@rowcount
end
No Comments
Be the first to comment!
| « Good discussion of SQL Server temp tables | Why do I hear a beep whenever I wash my hands in the bathroom? » |
Leave a Reply