Hierarchical SQL question

Question

I have a basic tree structure of entities. The tree can be a maximum of 5 nodes deep, but may be N nodes wide. I have mapped this relationship in table similar to what is shown below:

myID | myDescription | myParentID

I am starting out with a known object, which could translate to having a starting "myID". Now I want to get all the child nodes. Is there a way of getting all the child nodes in one statement? This needs to include the children of my children, and going on down the tree. I am using Oracle SQL.

Thanks, Jay

Answer 1

SELECT  *
FROM    mytable
START WITH
        myid = :id
CONNECT BY
        myparentid = PRIOR myid

Answer 2

I would suggest using another way to model your hierarchy if you want to retrieve all nodes in a single query. One very good, and common, implementation is the nested set model. The article outlines how this is implemented in MySQL, but it can easily be ported to Oracle.

Answer 3

A possible neat way to implement this is to add another field that contains the "path" to the record. Say the top record is ID=1. It has a child with ID=5, and it again has a child with ID=20, then the last record would have the path /1/5/20 So if you want all child nodes of you top node you do

select * from MyTable where Path like '/1/%'

(sorry, sql server syntax, I'm not an oracle developer - but the concept would still apply)

To get children of the middle node

select * from MyTable where Path like '/1/5/%'

The neat thing about that solution is that you can apply indexes to the "path" field, so the statement will execute using only a single index scan making it very efficient.