Difficulty with SQL query creation

Question

Difficulty with SQL query creation

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

I'm 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 instead of the numbers returned in pai and mae , return the names. However the parent and parent codes are ra as well and are stored in the same table. Is there any way to return names in place in ra with a single query?

sql

asked by anonymous 10.11.2015 / 16:54

2 answers

1

You can also 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

10.11.2015 / 17:00

How does the databinding and dirty-check of Angularjs work? How to convert pixel to dp?

score 3 · Accepted Answer

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 am joining the a with your parent (who is the p ) and also joining with the parent (who is the m ). I use LEFT OUTER JOIN so it does not exclude people who do not have registered parent (in these cases, the parent and / or the parent will be NULL ).