Difficulty with creating SQL query

Question

I am creating a view with the following query:

select 
    COD_PESSOA as ra,
    NOM_PESSOA as nome,
    COD_PESSOA_PAI as pai,
    COD_PESSOA_MAE as mae,
from PESSOA

This query returns a result like this:

ra      nome          pai      mae
1       ciclano       4        5
2       fulano        6        7
3       beltrano      8        9
6       joão          NULL     NULL
9       maria         NULL     NULL

I want in place of the numbers returned in pai and mae, return the names. But the codes of the father and mother are ra also and are stored in the same table. Is there any way to return the names instead in ra with a single consultation?

Answer 1

Try this:

SELECT
    a.cod_pessoa AS ra,
    a.nom_pessoa AS nome,
    p.nom_pessoa AS pai,
    m.nom_pessoa AS mae
FROM pessoa a
LEFT OUTER JOIN pessoa p ON p.cod_pessoa = a.cod_pessoa_pai
LEFT OUTER JOIN pessoa m ON m.cod_pessoa = a.cod_pessoa_mae

In this case, I’m joining the crowd a with your father (who is the person p) and also joining with the mother (who is the person m). Use the LEFT OUTER JOIN so that he does not exclude persons who have no registered father or mother (in these cases, the father and/or mother will be NULL).

Answer 2

It is also possible to use sub selects in the query:

select 
    PES.COD_PESSOA as ra,
    PES.NOM_PESSOA as nome,
    (select PAI.NOM_PESSOA from PESSOA as PAI where PAI.COD_PESSOA = PES.COD_PESSOA_PAI) as nome_pai,
    (select MAE.NOM_PESSOA from PESSOA as MAE where MAE.COD_PESSOA = PES.COD_PESSOA_MAE) as nome_mae
from PESSOA as PES