Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retrieve all children of a given node in a tree

Status
Not open for further replies.
jazun

jazun

Programmer
Jul 3, 2007
1
CA
Hi everyone,

I have a table which holds folders, each folder having a folderid, foldername and parentid (null for the root nodes). Now I'd like to select all the children of a node, having only the folderid of the parent node. I have the following query, having the folderid 1 as a parameter:

SELECT folderid, foldername FROM tblfolder
WHERE parentid = 1 OR
parentid IN (SELECT folderid FROM tblfolder WHERE parentid = 1 OR parentid IN
(SELECT folderid FROM tblfolder WHERE parentid = 1 OR parentid IN (SELECT folderid FROM tblfolder WHERE parentid=1)))

and so on.

The problem is this only returns the children up to a given depth, what other way would you recommend to retrieve all the children of the node, no matter how deep the tree goes?

Thanks.

Jason
 
Sort by date Sort by votes
you can either user recursion, or you can try looping...

recursion can be a bit faster, however if there's more than about 10/15 levels possible, then I'd suggest looping...

something along the lines of:

create temp table to hold results

fill temp table with 1st level.

for each folder in first level,
check if there are any sub folders, if so
insert into table sub folders.
call this function again for recursion...
next folder

--------------------
Procrastinate Now!
 
Upvote 0 Downvote
In SQL Server 2005 you could use recursive queries, something like:
Code: 
[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (ParentId [COLOR=blue]Int[/color], Id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1), [COLOR=blue]Name[/color] [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (0, [COLOR=red]'Parent 1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (0, [COLOR=red]'Parent 2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Child  1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Child  2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Child  3'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (1, [COLOR=red]'Child  4'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (3, [COLOR=red]'Grand Child  1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (3, [COLOR=red]'Grand Child  2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (3, [COLOR=red]'Grand Child  3'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (8, [COLOR=red]'Grand Grand Child  1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (8, [COLOR=red]'Grand Grand Child  2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test (ParentId, [COLOR=blue]Name[/color]) [COLOR=blue]VALUES[/color] (8, [COLOR=red]'Grand Grand Child  3'[/color])

;WITH TestMe(ParentId, Id, [COLOR=blue]Name[/color]) [COLOR=blue]AS[/color] 
   (
     [COLOR=blue]SELECT[/color] ParentId, Id , [COLOR=blue]Name[/color] [COLOR=blue]FROM[/color] @Test test1 [COLOR=blue]WHERE[/color] Id = 1
     UNION ALL
     [COLOR=blue]SELECT[/color] Test2.ParentId, Test2.Id , Test2.Name [COLOR=blue]FROM[/color] @Test test2
            [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] TestMe [COLOR=blue]ON[/color] Test2.ParentId = TestMe.Id
   )
[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] TestMe

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Code: 
DECLARE @ID int
SET @ID = 1

CREATE TABLE #Tree (
   FolderID int
)

INSERT #Tree SELECT @ID

WHILE @@RowCount > 0
   SELECT FolderID
   FROM tblFolder F
      INNER JOIN #Tree T ON F.ParentID = T.FolderID
      LEFT JOIN #Tree X ON T.FolderID = X.FolderID
   WHERE
      X.FolderID IS NULL

SELECT
   F.*
FROM
   tblFolder F
   INNER JOIN #Tree T ON F.FolderID = T.FolderID

DROP TABLE #Tree

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
705
mikrom
mikrom
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
180
grnzbra
grnzbra
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
238
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
418
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
333
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top