2

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

Improve this question

3 Answers 3

Reset to default
4 
SELECT  *
FROM    mytable
START WITH
        myid = :id
CONNECT BY
        myparentid = PRIOR myid
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Eric: Correct, CONNECT BY is Oracle only hierarchical query syntax.
@Eric J.: absolutely. In SQL Server and PostgreSQL 8.4 you use CTE's, for PostgreSQL 8.3- and MySQL read these articles in my blog: explainextended.com/2009/05/29/… , explainextended.com/2009/03/17/hierarchical-queries-in-mysql
0

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.

Improve this answer

1 Comment

Its a cool model, but not advisable for concurrent updates on large amount of data. Adding a leaf could update all records in table. If data changes rarely its a good advise.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.