Clever Geek Handbook

Hierarchical SQL question

I have a basic entity tree structure. The tree can contain at most 5 nodes, but it can be N nodes. I compared this ratio in a table similar to the one below:

myID | myDescription | myParentID

I start with a known object that can translate to run "myID". Now I want to get all the child nodes. Is there a way to get all child nodes in one expression? This should include my kids' kids and walk the tree. I am using Oracle SQL.

Thanks Jay

+2


source to share


3 answers 


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


+4


source


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



0


source


A neat way to do this is to add another field containing the "path" to the record. Let's say the top record is ID = 1. This has a child with ID = 5 and it again has a child with ID = 20, then the last record would have path / 1/5/20 So if you want all child nodes from you were above node, you do

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

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

To get medium sized node children

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

The best thing about this solution is that you can apply indices to the "path" field, so the statement will be executed using only one index scan, which makes it very efficient.

0


source






More articles:


All Articles