Relating various data from one table to another with SQL

Asked

Viewed 359 times

1

I needed to list, with SQL, a person’s father, mother and spouse along with their general data (e.g.: name, address, telephone, email, father, mother, spouse, status).

This data is in another dependent call table, each related to the person id

At first I thought it was very complicated, I even did searches on the internet, even here, without result, then I decided to burn the brain a little more, and I came to a super simple solution that satisfied 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 a better way is just put there in the comments and help the community

sql relationship

  • And what exactly is your doubt?

  • No doubt, I just wanted to share a solution to a problem

  • This query doesn’t have much mystery... that’s right.

  • @Isaiasfile you can do the following: explains a little better what were the problems you faced and post the solution (using alias and multiple joins for the same table) as answer.

  • 1

    The idea is for someone to do as you do: search for a particular issue on google or here and find your solution.

  • 2

    Just note that you have a comma remaining at the end of their fields select that may cause error in query execution.

  • Yeah... that comma was nonchalant at the time of putting here on the site :)

Show 2 more comments

1 answer

2

Some things that can be improved:

Spell out weak junction (left outer join)

This specifies that you are admitting that data does not always exist, which can happen. For example, a single person does not have a spouse. JOIN, by default, is a INNER JOIN. In the absence of any of the records, the line may come null.

LEFT OUTER JOIN ensures that the existing PESSOA will always come, even if the others don’t 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 a column, subselects may perform better

This actually depends on the technology of your database. I recommend a study of the execution plan to assess 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

Browser other questions tagged sql relationship

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