Join with the same table more than once

Question

Join with the same table more than once

Navigation

#1 by (2 votes)

0

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 the 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')

Will I need to create a stored procedure or a function to get the correct values?

mysql sql-select

asked by anonymous 04.01.2016 / 06:10

1 answer

Workaround / alternative for obsolete eregi_replace [duplicate] How to make the Google map dynamic

score 2 · Answer 1

Your query is a bit confusing, it took a while for me to understand what you wanted to do, but I think 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 original query is that you requested twice the same thing, just changing the alias of the returned field, instead of defining the aliases of the source.