How can I create a simple selection for my own reference table?

Question

For example, I have this table:

CREATE TABLE perarea ( id_area INT primary key, nombre VARCHAR2(200), id_areapadre INT references perarea(id_area) ); 

Instead of showing:

 1 IT null 2 Recursos Humanos null 3 Contabilidad 2 4 Legal 2 

I want:

 1 IT 2 Recursos Humanos 3 Contabilidad Recursos Humanos 4 Legal Recursos Humanos 

Any help?

I can’t understand for life how this choice will be.

Edit:

This SQL query works, but does not pull NAME, but only the parent id. Any help?

 select * from PerArea connect by id_area = id_areapadre; 

Answer 1

For reference, you can also do this without hierarchical extensions using self-join:

 SELECT p1.id_area, p1.name, COALESCE(p2.name, '') FROM perarea p1 LEFT JOIN perarea p2 ON (p1.id_areapadre = p2.id_area) 

Answer 2

This is a good example of a hierarchical query. Simple solution with CONNECT BY:

 SQL> SELECT id_area, nombre, PRIOR (nombre) 2 FROM perarea 3 CONNECT BY PRIOR (id_area) = id_areapadre 4 START WITH id_areapadre IS NULL; ID_AREA NOMBRE PRIOR(NOMBRE) -------- ----------------- ----------------- 1 IT 2 Recursos Humanos 3 Contabilidad Recursos Humanos 4 Legal Recursos Humanos 

Answer 3

It looks like you need a hierarchical query:

 select id_area, nombre, sys_connect_by_path(nombre,'/') from perarea start with id_areapadre is null connect by id_areapadre = prior id_area order by id_area 

Answer 4

Are you looking for a root name (CONNECT_BY_ROOT) or a substring path to display the name "parents"?

  SELECT id_area, nombre, PATHLEVEL , SUBSTR(PATHLEVEL,INSTR(PATHLEVEL,'/',-1,2)+1, INSTR(PATHLEVEL,'/',-1)-INSTR(PATHLEVEL,'/',-1,2)-1) PARENTNAME , rootNAME FROM( select id_area, nombre, sys_connect_by_path(nombre,'/') PATHLEVEL, CONNECT_BY_ROOT nombre rootNAME, from perarea start with id_areapadre is null connect by id_areapadre = prior id_area order by id_area );