Geek Answers Handbook

How to find all nodes in a subtree in a recursive SQL query?

I have a table that defines the child and parent relationships between nodes:

CREATE TABLE node (                           ' pseudo code alert
  id        INTEGER PRIMARY KEY,
  parentID  INTEGER, ' should be a valid id.
)

If it parentIDalways points to a valid existing node, then this will naturally determine the tree structure.

If parentID- NULL, then we can assume that node is the root of the node.

How would I:

  • Find all the nodes that are the decompressions of this node?
  • Find all nodes under a given node to a certain depth?

I would like to make each of them as one SQL (I expect it to be necessarily recursive) or two mutually recursive queries.

I do this in the ODBC context, so I cannot rely on any vendor-specific functions.

Edit

  • , / .
  • ; //, . , , .

.

+5
4

( ), . MySQL.

, , - /, . :

  • , , (, )
  • node .
+3

:

SQL -

+1

, , .

Celko SQL Smarties

+1

"" node , . . , 1 5, 17, , -1-5-17- .

, 5, , -5 -

, ID, , LIKE.

As for your problem with depth, if you add a depth column to your table with the current nesting depth, it will also become easy. You look at your starting depth node and then add x to it, where x is the number of levels you want to complete, and you filter records with more depth than that.

+1
source

More articles:


All Articles