Get All Last Level Children (leafs) From A Node (hierarhical Queries Oracle 11g)

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 w

Solution 1:

I think, something like that should do the trick:

SELECT*FROM
(SELECT n.id, n.val, CONNECT_BY_ISLEAF isleaf FROM NODES n 
       LEFTJOIN RELATION r ON n.id = r.id_child
CONNECTBY PRIOR n.id = r.id_father
STARTWITH r.id_father ISNULL)
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
WHERENOTEXISTS (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 
       LEFTJOIN RELATION r ON n.id = r.id_child
CONNECTBY PRIOR n.id = r.id_father
STARTWITH n.id =5)
WHERE isleaf =1

Solution 2:

You can simply use CONNECT_BY_ISLEAF.

SELECT n.id, n.val
FROM NODES n 
LEFTJOIN RELATION r ON (n.id = r.id_child)
WHERE CONNECT_BY_ISLEAF =1CONNECTBY PRIOR n.id = r.id_father
STARTWITH r.id_father ISNULL