Consulta recursiva SQL en la tabla de auto referencia (Oracle)

Supongamos que tengo esta muestra de datos: 

| 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         |

Me gustaría seleccionar todos los registros comienzan desde accountId = 1, por lo que el resultado esperado sería: 

| 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         |

Actualmente puedo hacer la selección recursiva, pero luego no puedo acceder a los datos de la referencia padre, por lo tanto no puedo devolver parent_name. El código que estoy usando es (adaptado al ejemplo simplista): 

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

¿Qué SQL debo usar para la recuperación mencionada anteriormente?

palabras clave Adicionales para futuros buscadores: SQL para seleccionar datos jerárquicos representados por claves principales en la misma tabla

Author: Cœur, 2010-02-23
Source

5 answers

Uso: 

    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
 33
Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-23 16:36:49

¿Qué hay de usar PRIOR,

So 

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

O si desea obtener el nombre raíz 

SELECT id, parent_id, CONNECT_BY_ROOT name
   FROM tbl 
START WITH id = 1 
CONNECT BY PRIOR id = parent_id
 11
Author: ari,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-08-12 09:50:26

Usando la nueva sintaxis de consulta anidada

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
 10
Author: PaulMurrayCbr,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-30 05:45:37

¿quieres hacer esto?

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

Editar Otra opción basada en la de OMG (pero creo que funcionará igualmente): 

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
 2
Author: Samuel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-23 16:07:29

Es un poco engorroso, pero creo que esto debería funcionar (sin la unión adicional). Esto supone que puede elegir un carácter que nunca aparecerá en el campo en cuestión, para que actúe como separador.

Puede hacerlo sin anidar el select, pero me parece un poco más limpio que tener cuatro referencias a SYS_CONNECT_BY_PATH.

select id, 
       parent_id, 
       case 
         when lvl <> 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)
 0
Author: Allan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-25 23:01:33