Solved

# Recursive query

Posted on 2004-11-14
339 Views
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
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...

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

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

LVL 18

Expert Comment

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

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.
0

LVL 18

Expert Comment

oops sorry bukko.   didnt spot that.
0

LVL 1

Author Comment

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

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   ;)
0

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.
0

## Featured Post

### 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.