Hierarchical Query to Self-Join Table Max 3 Times
<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>