Join with the same table more than once

Question

I’m having a problem performing a query.

The original table calls tbl_operacao, and contains the fields: id, tipo_operacao, idimovel, idproprietario, idlocatario, idseguro, idvalores.

When I call the values of idproprietario and idlocatario values come equal.

SELECT 
  `tbl_operacao`.`id`,
  `tbl_operacao`.`tipo_operacao`,
  `tbl_imovel`.`descricao`,
  `tbl_endereco`.`endereco`,
  `tbl_endereco`.`num`,
  `tbl_endereco`.`bairro`,
  `tbl_pessoas`.`nome`,
  `tbl_pessoas`.`sobrenome`,
  `tbl_pessoas`.`nome` as 'nome2'
FROM
  `tbl_operacao`
JOIN `tbl_imovel` 
  ON (`tbl_operacao`.`idimovel` = `tbl_imovel`.`id`)
JOIN `tbl_endereco` 
  ON (`tbl_operacao`.`idimovel` = `tbl_imovel`.`id` AND `tbl_imovel`.`idendereco` = `tbl_endereco`.`id`)
JOIN `tbl_pessoas` 
  ON (`tbl_operacao`.`idproprietario` = `tbl_pessoas`.`id`)
JOIN `tbl_pessoas` as ps2 
  ON (`tbl_operacao`.`idlocatario` = ps2.`id`)

I’ll need to create a stored Procedure or a Function to get the right values?

Answer 1

Your query It took me a while to figure out what you wanted to do, but I guess that’s it:

SELECT 
  `tbl_operacao`.`id`,
  `tbl_operacao`.`tipo_operacao`,
  `tbl_imovel`.`descricao`,
  `tbl_endereco`.`endereco`,
  `tbl_endereco`.`num`,
  `tbl_endereco`.`bairro`,
  pro.`nome`      AS proprietario_nome,
  pro.`sobrenome` AS proprietario_sobrenome,
  loc.`nome`      AS locatario_nome,
  loc.`sobrenome` AS locatario_sobrenome
FROM      `tbl_operacao`
LEFT JOIN `tbl_imovel`           ON `tbl_operacao`.`idimovel` = `tbl_imovel`.`id`
LEFT JOIN `tbl_endereco`         ON `tbl_imovel`.`idendereco` = `tbl_endereco`.`id`
LEFT JOIN `tbl_pessoas`  AS pro  ON `tbl_operacao`.`idproprietario` = pro.`id`
LEFT JOIN `tbl_pessoas`  AS loc  ON `tbl_operacao`.`idlocatario`    = loc.`id`

The problem with your query original is that you asked twice the same thing, only changing the alias of the returned field, instead of setting the aliases of origin.