How to use ONE query to find out tree structure?

[EZStudio04]

ID------------upperID----------Name------------------------isFolder
1------------ 0---------- Folder 1------------------------------------1
2------------ 1------------ Folder 1- Sub Folder--------------------1
3------------ 2------------ Folder1-Item1-A--------------------------0
4------------ 1------------ Folder 1- Sub Item-----------------------0


Hi all, if I have a table like above to demonstrate the folders and item relationship. This structure allows the user to create unlimited folders and items.

Now I would like to use one query to find out the tree structure of this table, how could I do the query.

Any help on this will be highly appreciated!

Thanks,


ez

[James74]

I had to do something similar. I used Nested sets with my sql. Here's a good example/tutorial:
http://www.codeproject.com/database/nestedsets.asp

[Grizzly9279]

Also, see this thread:

http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=7&threadid=1255788&ente...