Tree walk using a single SQL query?

Hi,

I would like to know if it is possible to do a tree walk where the connectivitiy information is stored in a database table.

1. I have a table that has colums like

Node_Id Parent_Id Description

What I would like to do is given a Node_Id, list all the rows whose Node_Id's are descendents of the Node_Id.

Is this possible using a single sql query? (I know I can do it in a program, PL/SQL or otherwise.)

2) If the above is not possible, will adding another column called Position help? The Position column orders the children of the given parent.

3) If that is also not possible what if the data in the table is

Node_Id Parent_Id Next_Sibling_Id

The Next_Sibling_Id is the next sibling or null if there is no more.

You may assume that the data in the table is all consistent and the data represents a tree (or a forest) i.e. it doesn't have loops and every node other than a root node has a parent and things like that.

And also, the query can be a complex one with sub-queries, joins and unions - just that it has to be one SQL statement.

P.S. One more thing, the depth of the tree is not fixed and I do not know what it is.

Thanks,
Young Seeker Received on Tue Dec 09 2003 - 17:00:40 CST