title: Find hierarchical or parent-child column values from a single table oracle
body: <p>I have a table name <strong>tree</strong> and it have two columns one is <strong>p</strong> and second one is <strong>ch</strong>.</p>
  
  <pre><code>p       ch 
  -------------
  1       2                   
  1       3 
  1       4 
  2       5 
  2       6 
  7       8 
  9       10 
  11      12 
  6       13 
  13      14 
  14      15 
  14      16
  </code></pre>
  
  <p>Output I want is all the linked child to a parent. If I give "1" as input for which I have to find all parent and child elements for example , in this case 1 is parent for 2,3,4 and 2 is parent for 5 and so on...in this case I need every linked child element and parent itself as mentioned below:</p>
  
  <pre><code>ParentChilds
  ------------
  1
  2
  3
  4
  5
  6
  13
  14
  15
  16
  </code></pre>
  
  <p>Below is the query I wrote and I want to confirm it is the best possible solution or we can do it better way, because i have large data in my table :</p>
  
  <pre><code>with LinkedAccounts (p, ch, orig_recur, dest_recur, lvl) as (
              select n.p
      , n.ch
      , 1 orig_recur
       , case n.p
             when 1 then ch
                     else p
          end dest_recur
       , 1 lvl
     from tree n
   where n.p = 1
     or n.ch = 1 union all
  select n.p
       , n.ch
      , LinkedAccounts.dest_recur orig_recur
       , case n.p
           when LinkedAccounts.dest_recur then n.ch
                             else n.p
         end dest_recur
       , LinkedAccounts.lvl + 1 lvl
    from LinkedAccounts
    join tree n
          on (n.p = LinkedAccounts.dest_recur and n.ch != LinkedAccounts.orig_recur)
          or (n.ch = LinkedAccounts.dest_recur and n.p != LinkedAccounts.orig_recur)
  )
   search breadth first by orig_recur, dest_recur set ordering
  cycle ch,
   p set is_cycle to '1' default '0'   select distinct p    from LinkedAccounts    union     Select Distinct ch    from LinkedAccounts;
  </code></pre>