Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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.
Thanks.
0
rot299
Asked:
rot299
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
bukkoCommented:
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...

bukko


CREATE FUNCTION dbo.get_section_leaf_count (
      @section_id      int
)  
RETURNS int AS  
BEGIN

      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
            BEGIN

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

            FETCH NEXT FROM leaf_cursor
            INTO      @leaf_id

            END

      RETURN      @count

END
0
 
KarinLoosCommented:
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
 begin
      -- initialise the output table the the first level records
      INSERT #output
      select childid , descr
      from #test
      where parentid = @FindChildrenFrom

      while @counter > 0
       begin
            -- 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      
       end
 end
-- output result
select * from #output


hope this helps
0
 
ShogunWadeCommented:
rot,    


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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bukkoCommented:
My example is recursive.

on the line
 SET @count = @count + dbo.get_section_leaf_count( @leaf_id )
the function calls itself.
0
 
ShogunWadeCommented:
oops sorry bukko.   didnt spot that.    
0
 
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?
0
 
ShogunWadeCommented:
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   ;)
0
 
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now