SQL recursive query on self referencing table (Oracle)

Question

Lets assume I have this sample data:

| Name     | ID | PARENT_ID | ----------------------------- | a1       | 1  | null      | | b2       | 2  | null      | | c3       | 3  | null      | | a1.d4    | 4  | 1         | | a1.e5    | 5  | 1         | | a1.d4.f6 | 6  | 4         | | a1.d4.g7 | 7  | 4         | | a1.e5.h8 | 8  | 5         | | a2.i9    | 9  | 2         | | a2.i9.j10| 10 | 9         | 

I would like to select all records start from accountId = 1, so the expected result would be:

| Name     | ID | PARENT_NAME | PARENT_ID |  ------------------------------------------- | a1       | 1  | null        | null      | | a1.d4    | 4  | a1          | 1         | | a1.e5    | 5  | a1          | 1         | | a1.d4.f6 | 6  | a1.d4       | 4         | | a1.d4.g7 | 7  | a1.d4       | 4         | | a1.e5.h8 | 8  | a1.e5       | 5         | 

I am currently able to make the recursive select, but then I can't access the data from the parent reference, hence I can't return parent_name. The code I'm using is (adapted to the simplistic example):

SELECT id, parent_id, name FROM tbl    START WITH id = 1    CONNECT BY PRIOR id = parent_id 

What SQL should I be using to the mentioned above retrieval?

Additional key words for future seekers: SQL to select hierarchical data represented by parent keys in same table

Answer 1

What about using PRIOR,

so

SELECT id, parent_id, PRIOR name    FROM tbl  START WITH id = 1  CONNECT BY PRIOR id = parent_id`

or if you want to get the root name

SELECT id, parent_id, CONNECT_BY_ROOT name    FROM tbl  START WITH id = 1  CONNECT BY PRIOR id = parent_id

Answer 2

Use:

    SELECT t1.id,             t1.parent_id,             t1.name,            t2.name AS parent_name,            t2.id AS parent_id       FROM tbl t1  LEFT JOIN tbl t2 ON t2.id = t1.parent_id START WITH t1.id = 1  CONNECT BY PRIOR t1.id = t1.parent_id