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!

Kondon

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

Harlamert

Thank you too much for your answe, but in your way you obtain all the last level children of all tree, and I only want the last children which come from a selected node. – Kondon

I don´t know the condition to get it. – Kondon

Sorry I cannot vote you because I don´t have enough reputation. Thank you another time! – Kondon

Answer 2

You can simply use CONNECT_BY_ISLEAF.

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

Heliograph