Removing duplicate subtrees from CONNECT-BY query in oracle

Question

I have a hierarchical table in the format

CREATE TABLE tree_hierarchy ( id NUMBER (20) ,parent_id NUMBER (20) ); INSERT INTO tree_hierarchy (id, parent_id) VALUES (2, 1); INSERT INTO tree_hierarchy (id, parent_id) VALUES (4, 2); INSERT INTO tree_hierarchy (id, parent_id) VALUES (9, 4); 

When I run the query: -

 SELECT id,parent_id, CONNECT_BY_ISLEAF leaf, LEVEL, SYS_CONNECT_BY_PATH(id, '/') Path, SYS_CONNECT_BY_PATH(parent_id, '/') Parent_Path FROM tree_hierarchy WHERE CONNECT_BY_ISLEAF<>0 CONNECT BY PRIOR id = PARENT_id ORDER SIBLINGS BY ID; 

The result that I get is as follows: -

 "ID" "PARENT_ID" "LEAF" "LEVEL" "PATH" "PARENT_PATH" 9 4 1 3 "/2/4/9" "/1/2/4" 9 4 1 2 "/4/9" "/2/4" 9 4 1 1 "/9" "/4" 

But I need an Oracle Sql query that only gets me from this

 "ID" "PARENT_ID" "LEAF" "LEVEL" "PATH" "PARENT_PATH" 9 4 1 3 "/2/4/9" "/1/2/4" 

This is a simpler example. I have over 1000 entries this way. When I run the above query, it generates a lot of duplicates. Can someone give me a general query that will give the full path from leaf to root without duplicates.Thanks output for help in advance

Answer 1

The root of the node in the final hierarchy must always be known. By definition: http://en.wikipedia.org/wiki/Tree_structure the root of a node is a node that has no parents. To check if this node is the root node, take "parent_id" and check the table if there is an entry with this identifier. The request may look like this:

 SELECT id,parent_id, CONNECT_BY_ISLEAF leaf, LEVEL, SYS_CONNECT_BY_PATH(id, '/') Path, SYS_CONNECT_BY_PATH(parent_id, '/') Parent_Path FROM tree_hierarchy th WHERE CONNECT_BY_ISLEAF<>0 CONNECT BY PRIOR id = PARENT_id START WITH not exists ( select 1 from tree_hierarchy th1 where th1.id = th.parent_id ) ORDER SIBLINGS BY ID; 

Answer 2

You must specify id explicitly to build the path. Now your request builds a path for all leaves that satisfy your condition. You need to use "start with". Let's try this as follows:

 SELECT id,parent_id, CONNECT_BY_ISLEAF leaf, LEVEL, SYS_CONNECT_BY_PATH(id, '/') Path, SYS_CONNECT_BY_PATH(parent_id, '/') Parent_Path FROM tree_hierarchy WHERE CONNECT_BY_ISLEAF<>0 CONNECT BY PRIOR id = PARENT_id START WITH id = 2 ORDER SIBLINGS BY ID;