Recursive query

I have a simple parent / child relationship setup in a table using the fields "Id" and "parentId" to create the relationship. If I pass an "Id", Is it possible to run a query that would recursively get all rows underneath it and the ones underneath them and so on til there are no more child elements?

Any examples would be great.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This is a UDF I wrote some time ago to do a similar thing to what you want, I think.
It is to count the leaves of a self referencing table called 'Section'.

Hope it helps...


CREATE FUNCTION dbo.get_section_leaf_count (
      @section_id      int

      DECLARE      @count      int,
                  @leaf_id int

      SET      @count = 0

      -- Add number of leaf nodes for this section
      -- i.e.: the number of children which do not themselves have children
      SELECT      @count = @count + ISNULL( SUM( 1 ), 0 )
      FROM            dbo.section      section_1
      WHERE      section_1.parent_section_id      = @section_id
            SELECT      section_2.*
            FROM            dbo.section      section_2
            WHERE      section_2.parent_section_id      = section_1.section_id

      -- Now add in the leaf nodes for all child nodes, recursively
      DECLARE      leaf_cursor CURSOR FOR
      SELECT      dbo.section.section_id
      FROM      dbo.section
      WHERE      dbo.section.parent_section_id = @section_id

      OPEN leaf_cursor

      FETCH NEXT FROM leaf_cursor
      INTO      @leaf_id


            SET @count = @count + dbo.get_section_leaf_count( @leaf_id )

            FETCH NEXT FROM leaf_cursor
            INTO      @leaf_id


      RETURN      @count

here is an example, try this is query analyser

Create table #test (childid int, parentid int NULL, descr varchar(20) )
insert #test values ( 1, NULL, 'TopA' )
insert #test values ( 2, NULL, 'TopB' )
insert #test values ( 3, 1, 'childOf_1_A' )
insert #test values ( 4, 2, 'childOf_1_B' )
insert #test values ( 5, 3, 'ChildOf_3_A' )
insert #test values ( 6, 4, 'ChildOf_4_B' )
insert #test values ( 7, 5, 'ChildOf_5_A' )
insert #test values ( 8, 6, 'ChildOf_6_B' )
-- now to make things more complicated
-- add multiple child roots from the top root
insert #test values ( 9, 1, 'childOf_1_A' )
insert #test values ( 10, 9, 'ChildOf_9_A' )
insert #test values ( 11, 10, 'ChildOf_10_A' )

Declare @counter int
Declare @FindChildrenFrom int
Declare @id int
-- create a temporary table to hold the results while looping
create table #output (foundchild int , descr varchar(20) )
-- set the find from top parent
set @FindChildrenFrom = 1
-- first determine if there are any children at all
select @counter = Count(*) from #test where parentid = @FindChildrenFrom
If @counter > 0
      -- initialise the output table the the first level records
      INSERT #output
      select childid , descr
      from #test
      where parentid = @FindChildrenFrom

      while @counter > 0
            -- Loop while there are found child records which appear
            -- as parents in the main table
            INSERT #output
            select a.childid , a.descr
            from #test       a
                              join #output    b ON (b.foundchild = a.parentid)
            where a.childid not in (select foundchild
                                                                 from #output x
                                                                 where x.foundchild = a.childid)
                      -- recheck the counter for looping purposes
            set @counter = @@Rowcount      
-- output result
select * from #output

hope this helps

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Just a little note:   You will notice that these solutions are itterative rather than recursive.      SQL Server is pretty restrictive when it comes to recursion, yes it can be done but only to 16 levels.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

My example is recursive.

on the line
 SET @count = @count + dbo.get_section_leaf_count( @leaf_id )
the function calls itself.
oops sorry bukko.   didnt spot that.    
rot299Author Commented:
Thanks for all your help.
Im not advanced at SQL so KarinLoos' example is more straight forward and easier for me to understand so I think I will use that example. Is there any reason why the UDF would be a better choice?
It depends on how you want to use it:

With a function you can refer to the function within a select statement

EG: SELECT * FROM MyFunction()    - for rowset functions
SELECT dbo.MyFunction2()  --for single value return functions

but you cant do that with stored procedures.    

In other words if you waht to take the results and build upon them then it is better in a function   If all you want to do is return the results to a client its better in stored proc.

of course you can have the dest of both worlds by having it in a function and then have a sotre proc that calls the function (but not the oposite way).

Confused ? Me 2   ;)
rot299Author Commented:
Ill stick with the sp for the moment as it does what I need. I might look into the UDF at a later stage.

Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.