Recursive query

Posted on 2004-11-14
Last Modified: 2010-05-18
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.
Question by:rot299
    LVL 8

    Assisted Solution

    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
    RETURNS int AS  

          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
          AND NOT EXISTS (
                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

          WHILE @@FETCH_STATUS = 0

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

                FETCH NEXT FROM leaf_cursor
                INTO      @leaf_id


          RETURN      @count

    LVL 13

    Accepted Solution

    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
    LVL 18

    Expert Comment


    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.
    LVL 8

    Expert Comment

    My example is recursive.

    on the line
     SET @count = @count + dbo.get_section_leaf_count( @leaf_id )
    the function calls itself.
    LVL 18

    Expert Comment

    oops sorry bukko.   didnt spot that.    
    LVL 1

    Author Comment

    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?
    LVL 18

    Expert Comment

    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   ;)
    LVL 1

    Author Comment

    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.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now