Removing duplicate subtrees from CONNECT-BY query in oracle

database, oracle, sql

Solution

The root node in finite hierarchy must be always known. According to the definition: http://en.wikipedia.org/wiki/Tree_structure the root node is a node that has no parents. To check if a given node is a root node, take "parent_id" and check in the table if exists a record with this id. The query might 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;

Problem

I have a heirarchical 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; ``` Result I am Getting is like this:- ``` "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 gets me only 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 more that 1000 records in such fashion.When I run the above query,It is generating many duplicates.Can any one give me a generic query that will give complete path from leaf to root with out duplicates.Thanks for the help in advance

Original source