SQL select with two or more occurrences of a table on the same row?

Question

SQL select with two or more occurrences of a table on the same row?

Navigation

#1 by (1 votes)

1

In a relational bank terminations has 1 contract, the contract has 1 property and two people (lessor) in>) how do I prepare SELECT to return these two people on the same line?

SELECT rescisoes.rescisao_id as id,
   rescisoes.data_desocupaçao as desocupação,
   contratos.controle as controle,
   imoveis.logradouro as endereço,
   pessoas.nome as locador, 
   pessoas.nome as locatário 
FROM rescisoes 
  LEFT JOIN contratos ON contratos.contrato_id = rescisoes.contrato_id 
  LEFT JOIN imoveis ON imoveis.imovel_id = contratos.imovel_id 
  LEFT JOIN pessoas ON contratos.locador_pessoa_id = pessoas.pessoa_id
                  OR contratos.locatario_pessoa_id = pessoas.pessoa_id

This way, it returns two rows each with lessor and lessee with the same name.

mysql

asked by anonymous 14.04.2017 / 14:40

1 answer

Summing up with a child's value from firebase? One-way Relationship OneToMany

score 1 · Accepted Answer

SELECT rescisoes.rescisao_id as id,
   rescisoes.data_desocupaçao as desocupação,
   contratos.controle as controle,
   imoveis.logradouro as endereço,
   locador.nome as locador, 
   locatario.nome as locatário 
FROM rescisoes 
  LEFT JOIN contratos ON contratos.contrato_id = rescisoes.contrato_id 
  LEFT JOIN imoveis ON imoveis.imovel_id = contratos.imovel_id 
  LEFT JOIN pessoas locador ON contratos.locador_pessoa_id = locador.pessoa_id
    LEFT JOIN pessoas locatario ON contratos.locatario_pessoa_id = locatario.pessoa_id