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