Relating multiple data from one table to one data from another with SQL

Navigation
1

I needed to list, with SQL, the father, mother and spouse of a person together with their general data (eg name, address, telephone, email, father, mother, spouse, status).

This data is in another table called dependent, each one related to the id of the person

At first I thought it was very complicated, I started to search the internet, even here, with no result, so I decided to burn the brain a bit, and I came up with a super simple solution that suited my need: 

SELECT
    p.nome,
    p.endereco,
    p.telefone,
    p.email,
    dp.nome AS nomePai,
    dm.nome AS nomeMae,
    dc.nome AS conjuge,
    p.status,
FROM
    pessoa p
    JOIN dependente dp ON dp.idPessoa = p.idPessoa AND dp.tipo = 6
    JOIN dependente dm ON dm.idPessoa = p.idPessoa AND dm.tipo = 7
    JOIN dependente dc ON dc.idPessoa = p.idPessoa AND dc.tipo = 5

Who knows better, just put it there in the comments and help the community

    
asked by anonymous 13.05.2015 / 18:50

1 answer

2

Some things that can be improved:

Explain weak join ( left outer join )

This specifies that you are admitting that data does not always exist, which can happen. For example, a single person has no spouse. JOIN , by default, is INNER JOIN . If none of the records exist, the row may be null.

LEFT OUTER JOIN ensures that the existing line in PESSOA will always come, even if the others do not exist. 

SELECT
    p.nome,
    p.endereco,
    p.telefone,
    p.email,
    dp.nome AS nomePai,
    dm.nome AS nomeMae,
    dc.nome AS conjuge,
    p.status,
FROM
    pessoa p
    LEFT OUTER JOIN dependente dp ON dp.idPessoa = p.idPessoa AND dp.tipo = 6
    LEFT OUTER JOIN dependente dm ON dm.idPessoa = p.idPessoa AND dm.tipo = 7
    LEFT OUTER JOIN dependente dc ON dc.idPessoa = p.idPessoa AND dc.tipo = 5

Being just one column, subselects may perform better

This actually depends on the technology of your database. I recommend a study of the execution plan to evaluate the cost of both.

Your sentence can be written like this: 

SELECT
    p.nome,
    p.endereco,
    p.telefone,
    p.email,
    (SELECT dp.nome FROM dependente dp WHERE dp.idPessoa = p.idPessoa AND dp.tipo = 6) AS nomePai,
    (SELECT dm.nome FROM dependente dm WHERE dm.idPessoa = p.idPessoa AND dm.tipo = 7) AS nomeMae,
    (SELECT dc.nome FROM dependente dc WHERE dc.idPessoa = p.idPessoa AND dc.tipo = 5) AS conjuge,
    p.status,
FROM
    pessoa p
    
13.05.2015 / 19:26
Problem with database insertion Failed to Change Directory - Indy 10 - IdFTP