Jeremy Stein - Brain

« »

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

April 10, 2006 No Comments.

No Comments

Be the first to comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Why ask?

« »