Query including multiple tables

Question

Query including multiple tables

Asked 5 years, 3 months ago

Viewed 95 times

0

I have a people table that has people from funcionarios, pais and maes, I’ve managed to relate everyone.

Just look at the way it returns. I need the maes stay in the first line, just like the pais.

SELECT  DISTINCT mae.nomepessoa as Mae,''as Pai FROM  dbo.bpm_pessoa mae 
 INNER JOIN dbo.bpm_pessoarelacionamento maerelacionamento on
  maerelacionamento.idpessoaprincipal = mae.idpessoa where maerelacionamento.idtiporelacionamentopessoa = 3 
UNION 
SELECT DISTINCT '' as pai,pai.nomepessoa as pai FROM dbo.bpm_pessoa AS  pai 
INNER JOIN dbo.bpm_pessoarelacionamento pairelacionamento on 
pairelacionamento.idpessoaprincipal = pai.idpessoa where pairelacionamento.idtiporelacionamentopessoa = 2
)
AS RESULTADO

1

Can you post a summary structure of your tables and what is the expected result? Only with queries is it not clear to me.

– gmsantos

2019/03/18 at 18:30
It is the following : the table (dbo.bpm_pessoa) has the data of several people.. To know exactly who is a 'employee or parent of the employee or parent of the employee' this relation is in the table (dbo.bpm_pessoarelationing) where 'idtiporelacionamentopessoa' says the type of people. If it is 2 then the person is 'Father' if it is 3 then it is 'Mother'. I have already managed to do the references.. only it is displaying incorrectly.. i want both parent and parent information to be displayed in the first lines..

– Fabio Henrique

2019/03/18 at 18:37
And how to get only the employee lines from the bpm_person table?

– José Diz

2019/03/18 at 18:59

2 answers

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.

by Alberto Cláudio Mandlate • **399** points · Answer 1 · 2019-03-27T08:47:24+00:00

SELECT Resultado.mae,Resultado.Pai
FROM(
    SELECT DISTINCT 1 As Ordem,mae.nomepessoa as Mae,''as Pai
    FROM dbo.bpm_pessoa As mae 
    JOIN dbo.bpm_pessoarelacionamento As maerelacionamento
        ON maerelacionamento.idpessoaprincipal = mae.idpessoa
    WHERE maerelacionamento.idtiporelacionamentopessoa = 3 
    UNION 
    SELECT DISTINCT 2 As Ordem,'' as pai,pai.nomepessoa as pai
    FROM dbo.bpm_pessoa AS pai
    JOIN dbo.bpm_pessoarelacionamento As pairelacionamento
        ON pairelacionamento.idpessoaprincipal = pai.idpessoa
    WHERE pairelacionamento.idtiporelacionamentopessoa = 2
)
AS RESULTADO
ORDER BY Resultado.Ordem,Resultado.mae,Resultado.pai

by João Martins • **5,597** points · Answer 2 · 2019-03-18T18:45:51+00:00

Using two LEFT JOIN can perfectly relate the 3 tables:

SELECT      bpm.nomepessoa              as pessoa
        ,   ISNULL(mae.nomepessoa, '')  AS mae
        ,   ISNULL(pai.nomepessoa, '')  AS pai
FROM        bpm_pessoa  bpm
LEFT JOIN   (
                SELECT      ps.idpessoa
                        ,   ps.nomepessoa
                FROM        bpm_pessoa                  ps
                INNER JOIN  bpm_pessoarelacionamento    pr  ON  pr.idpessoaprincipal            = ps.idpessoa
                                                            AND pr.idtiporelacionamentopessoa   = 3
            )           mae ON mae.idpessoa = bpm.idpessoa
LEFT JOIN   (
                SELECT      ps.idpessoa
                        ,   ps.nomepessoa
                FROM        bpm_pessoa                  ps
                INNER JOIN  bpm_pessoarelacionamento    pr  ON  pr.idpessoaprincipal            = ps.idpessoa
                                                            AND pr.idtiporelacionamentopessoa   = 2
            )           pai ON pai.idpessoa = bpm.idpessoa