title: Hierarchical Query to Self-Join Table Max 3 Times
body: <p>I have an Oracle DB with a table with the following columns:</p>
  
  <pre><code>    ID | PARENTID | DETAIL1
      ------------------------
      1  | NULL     | BLAH1
      2  | 1        | BLAH2
      3  | 2        | BLAH3
      4  | 2        | BLAH4
      5  | NULL     | BLAH5
      6  | 5        | BLAH6
      7  | 6        | BLAH7
      8  | 5        | BLAH8
      9  | 5        | BLAH9
      10 | 8        | BLAH10
  </code></pre>
  
  <p>I prepared a self-join for </p>
  
  <pre><code>    SELECT    PARENT.ID AS "PID",
                PARENT.DETAIL1 AS "PDETAIL1",
                CHILD.ID AS "CID",
                CHILD.DETAIL1 AS "CDETAIL1" 
  
        FROM    table1 CHILD
  
        LEFT OUTER JOIN table1 PARENT
  
        ON      PARENT.ID = CHILD.PARENTID
        WHERE   PARENTID IS NOT NULL;
  </code></pre>
  
  <p>The output looks as shown below:</p>
  
  <pre><code>    PID | PDETAIL1 | CID | CDETAIL1|
      --------------------------------
      1   | BLAH1    | 2   | BLAH2   |
      2   | BLAH2    | 3   | BLAH3   |
      2   | BLAH2    | 4   | BLAH4   |
      5   | BLAH5    | 6   | BLAH6   |
      6   | BLAH6    | 7   | BLAH7   |
      5   | BLAH5    | 8   | BLAH8   |
      5   | BLAH5    | 9   | BLAH9   |
      8   | BLAH8    | 10  | BLAH10  |
  </code></pre>
  
  <p>Pretty straight forward. I would like to know if this self join can be done as a hierarchical/recursive query. The maximum nesting depth is 3. The target output should look like this:</p>
  
  <pre><code>    GPID | GPDETAIL1 | PID | PDETAIL1 | CID  | CDETAIL1 |
      ---------------------------------------------------
      1    | BLAH1     | 2   | BLAH2    | 3    | BLAH3    |
      1    | BLAH1     | 2   | BLAH2    | 4    | BLAH4    |
      5    | BLAH5     | 6   | BLAH6    | 7    | BLAH7    |
      5    | BLAH5     | 8   | BLAH8    | 10   | BLAH10   |
      5    | BLAH5     | 9   | BLAH9    | NULL | NULL     |
  </code></pre>
  
  <p>Google isn't helping me, there is a ton of information related to hierarchical queries, but nothing including self-joins AND hierarchical queries and most questions appear to be similar (on the surface), but nothing guiding me to what I need. I'm a SQL newbie so unless the answer is specific, I could be missing it.</p>