How can I create a simple Select for a self referential 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 for the life of me figure out how this select would be.

Edit:

This SQL Query works, but doesn't pull the NAME, only the ID of the parent. Any help?

select * from PerArea
connect by id_area = id_areapadre;

Answer 1

For reference, you could also do it without hierarchical extensions by using a 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. A simple solution with a 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