CODEWITHSUNDEEP
sqlpostgresqloracle11ghierarchical-datarecursive-query
Sergey Morozov
Sergey Morozov

Reputation: 4608

How to get tree by any node(SQL)

I have self referenced table - HIERARCHY(id, name, parent_id). So I need to get all the hierarchy by any node of this hierarchy. For example we have tree, where h1, h2 are roots:

-(h1)
  | |_(h1_1)
  | |   |_(h1_1_2)
  | |_(h1_2)
  |    |_(h1_2_1)
-(h2)
  | |_(h2_1)
  | |_(h2_2)   
  |

What I need, it to get all the tree e.g with root h1 by any node of this tree e.g. by h1_2

     -(h1)
        |_(h1_1)
get     |   |_(h1_1_2)    by h1_2 or h1_2_1, etc
        |_(h1_2)
           |_(h1_2_1)

I wrote the query:

WITH RECURSIVE hierarchy_with_parents(id) AS (
  SELECT l.id, l.name, l.parent_id FROM hierarchy AS l WHERE l.id = <any_row_id>
  UNION ALL
  SELECT lc.id, lc.name, lc.parent_id FROM hierarchy lc, hierarchy_with_parents lwp WHERE lc.id = lwp.parent_id
), hierarchy_with_children(id) AS (
  SELECT l.id, l.name, l.parent_id FROM hierarchy AS l WHERE l.id 
  IN ( -- sub-query for getting parent id
    SELECT
    lwp.id
    FROM hierarchy_with_parents AS lwp
    WHERE lwp.parent_id IS NULL
  )
  UNION ALL
  SELECT lc.id, lc.name, lc.parent_id FROM hierarchy lc, hierarchy_with_children lwc WHERE lc.parent_id = lwc.id
)
SELECT * FROM hierarchy_with_children

hierarchy_with_parents - returns subtree from child to parent(inclusive),
hierarchy_with_children - returns all tree.

It seems all works Ok, but I am not DB-expert and I want to know limitations and comments about my query. Also any other solutions for PostgreSQL and Oracle 11g welcome.

Thanks.

Upvotes: 0

Views: 887

Answers (1)

user5683823
user5683823

Reputation:

Given :input_node, first find the root of the subtree containing that node. This is done in the find_root factored subquery. Then simply collect all the rows that link to that root. I need the NVL() call in the outer query in case the :input_node was a root already; in that case find_root returns no rows. When used as a scalar subquery, that is treated as null (at least in Oracle), so I can use NVL() to fix it.

with
     hierarchy as (
       select 2 as child, 1  as parent from dual union all
       select 3, 2 from dual union all
       select 4, 1 from dual union all
       select 5, 4 from dual union all
       select 7, 6 from dual union all
       select 8, 7 from dual union all
       select 9, 6 from dual
     ),
     find_root as (
       select parent as rt
       from hierarchy
       where connect_by_isleaf = 1       
       start with child = :input_node
       connect by child = prior parent
     ) 
select child, parent
from   hierarchy
start with parent = nvl((select rt from find_root), :input_node)
connect by parent = prior child;

Upvotes: 1

Related Questions