get ALL last level children (leafs) from a node (hierarhical queries Oracle 11G)

Question

I am trying and searching the way to get ALL last level children (leafs) from a node, in a hierchical query in Oracle 11g database.

I have 2 tables: "Nodes" (A list of all nodes with their respective value), and "Relation" which specify father-child relation:

---

--NODES--

 ID_NODE    -      VALUE
1       3
2       6
3       9
4       2
5       4
6       5
7       2
8       7
9       8
10      1

---

--RELATION--

ID_FATHER    -   ID_CHILD
1       2
1       3
1       4
2       5
2       6
4       7
5       8
5       9
7       10  

I have read about CONNECT_BY_ISLEAF which returns 1 if it is a leaf, but I cannot query CONNECT_BY_ISLEAF like the Oracle example, and I don´t get any result. Even though I don´t know exactly how to make the query exactly with this function (Using case condition for example?)

Thank you too much!

Answer 1

I think, something like that should do the trick:

SELECT * FROM
(SELECT n.id, n.val, CONNECT_BY_ISLEAF isleaf FROM NODES n 
       LEFT JOIN RELATION r ON n.id = r.id_child
CONNECT BY PRIOR n.id = r.id_father
START WITH r.id_father IS NULL)
WHERE isleaf = 1

---

Oh, and by the way, you can get all leafs without even using hierahical query. Just select all nodes, which are not father's node for any node from relation table. Something like that:

SELECT n.* FROM NODES n
WHERE NOT EXISTS (SELECT ID_FATHER FROM RELATION r
                  WHERE r.id_father = n.id)

---

In order to get the leaf nodes from the specified node, just change condition in START WITH clause, to start tree reverse from the node you're interested in. For example, this query will return you all children leafs of node with id = 5:

SELECT * FROM
(SELECT n.id, n.val, CONNECT_BY_ISLEAF isleaf FROM NODES n 
       LEFT JOIN RELATION r ON n.id = r.id_child
CONNECT BY PRIOR n.id = r.id_father
START WITH n.id = 5)
WHERE isleaf = 1