Geek asks and answers

Get ALL last level children (leaves) from node (Oracle 11G hierarchical queries)

I am trying to find a way to get ALL the last child elements (sheets) from a node , in a hierarchical query in an Oracle 11g database.

I have 2 tables: "Nodes" (a list of all nodes with their own value) and "Relationship", which determine the father-child relationship:

- NODES -

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

- ATTITUDE -

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

I read about CONNECT_BY_ISLEAF, which returns 1 if it is a sheet, but I cannot query CONNECT_BY_ISLEAF as an Oracle example, and I am not getting any result. Although I don’t know exactly how to make a request with this function (for example, using a condition condition?)

Thank you very much!

+3
source share
2

, - :

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

, , , , . , node node . - :

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

node, START WITH, node, . , node 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
+7

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
+1

More articles:


All Articles