Hierarchical Query in Oracle SQL

Questions

I have data like this:

<img src="https://i.stack.imgur.com/HN4ag.png" alt="Data Table">

From the above table I am trying to write a SQL using connect by clause to get a hierarchy like this

      MAINCONTENT

       SPECIAL
        RIDGE
         SALESCONTENT

       ANOTHERONE
        RODGE
         SOMETHING ELSE
          ANOTHER
        ...

So far this SQL below is showing my only all the children of MAINCONTENT but i want to do it without passing the parameter. Also this below one is not showing me the Children of Children, meaning its not doing recursive.

    select DISTINCT parent from  MYTABLE
    connect by prior CHILD = PARENT
    start with PARENT =  MAINCONTENT ;

Answers

You seem to be showing only part of the data in the spreadsheet, so I m not sure if the below is 100% correct, but:

First get rid of indirect links (direct ones should cover the entire tree) and create extra entries for top-level records. Then apply hierarchical clause.

Try the below:

WITH mytable_normalized AS (
  SELECT parent, child
    FROM mytable
   WHERE direct_link =  Y 
   UNION ALL
  SELECT null, parent
    FROM mytable
   MINUS
  SELECT null, child
    FROM mytable
)
SELECT lpad(   , level*2) || child
  FROM mytable_normalized
CONNECT BY prior child = parent
 START WITH parent IS NULL;

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/25191801/hierarchical-query-in-oracle-sql

Related