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

Asked

Viewed 203 times

1

In a relational bank terminations have 1 contract, the contract has 1 real estate and two people(landlord and renter) how do I prepare the 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

thus returns two lines each with lessor and lessee with the same name.

mysql
  • 1

    Enter the table "people" two times one for the renter and the other for the lessor , will be two joins , give different aliases.

  • https://forum.imasters.com.br/topic/557015-select-com-related-de-n-n/? do=findComment&comment=2222123

  • I have already thought about this, however from the following error: #1066 - Table/alias 'people' not unique unless you have to do some more detail to work LEFT JOIN people ON contracts.renter_person=id people.personLEFT JOIN people ON contracts.locatario_pessoa_id = people.person_id

  • LEFT JOIN people locatario ON contracts.locatario_pessoa_id = locatario . people_id LEFT JOIN people lessor ON contracts.personal renter_id = lessor.people_id using locator and lessor aliases to name the columns

  • Thanks guy that’s it! if you can answer the question with this comment above for me to mark as final answer I thank you!

  • SELECT termination.terminations_id as id, termination.data_vacate vacating, contracts.control as control, property.path the address, lessor.name the lessor, 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 contracts.personal locatario_id = personal locatario_id

Show 1 more comment

1 answer

1


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

Browser other questions tagged mysql

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