Root and leaf node from a hierarchical tree.

From: babu <babu2244_at_hotmail.com>
Date: 22 Sep 2005 06:12:34 -0700
Message-ID: <1127394754.013472.313750@g47g2000cwa.googlegroups.com>

I am working on oracle 9i. I have a table that has several tree strucutres stored in it. Tree strucutres can have any arbitrary depth. Root node has parent_id as null.

For Example :

Parent_Id Node_Id

            rootA
rootA       A-child1
A-child1    A-leaf
            rootB
rootB       B-child1
rootB       B-child12

B-child1 B1-leaf
B-child12 B12-leaf

In a single sql statement, I want to get the root node and the leaf node from this tree, I do not want other nodes in between.

So the result I need is

---

rootA A-leaf
rootB B1-leaf
rootB B12-leaf.

I am using connect by and start with in the select statement, but I am not able to figure out, how to extract such a result with just one sql statement.As the data is on production and I can not write any function or programme
there. I need to have this data only using sql query. I can not use connect-by-root as I am on 9i.

Please let me know whether it is possible or not.

Thanks, Received on Thu Sep 22 2005 - 08:12:34 CDT