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

User · Accepted Answer

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

User · Answer

It s a little on the cumbersome side  but I believe this should work  without the extra join    This assumes that you can choose a character that will never appear in the field in question  to act as a separator   You can do it without nesting the select  but I find this a little cleaner that having four references to SYS CONNECT BY PATH   select id          parent id          case           when lvl  lt  gt  1           then substr name path                       instr name path     1 lvl-1  1                       instr name path     1 lvl                        -instr name path     1 lvl-1 -1            end as name  from     SELECT id  parent id  sys connect by path name      as name path  level as lvl   FROM tbl    START WITH id   1    CONNECT BY PRIOR id   parent id

User · Answer

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

User · Answer

Using the new nested query syntax  with q name  id  parent id  parent name  as       select        t1 name  t1 id         null as parent id  null as parent name      from t1     where t1 id   1   union all     select        t1 name  t1 id         q id as parent id  q name as parent name      from t1  q     where t1 parent id   q id   select   from q

User · Answer

Do you want to do this   SELECT id  parent id  name     select Name from tbl where id   t parent id  parent name FROM tbl t start with id   1 CONNECT BY PRIOR id   parent id   Edit Another option based on OMG s one  but I think that will perform equally    select             t1 id              t1 parent id              t1 name             t2 name AS parent name             t2 id AS parent id from       select id  parent id  name     from tbl     start with id   1      connect by prior id   parent id  t1     left join     tbl t2 on t2 id   t1 parent id

[sql] SQL recursive query on self referencing table (Oracle)

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to plsql

Examples related to hierarchical-data