I need to put a left join to more in this query

Question

I need to put a left join to more in this query

Navigation

#1 by (3 votes)

0

My code is below it returns me "h.banco as the bank" with a number, which is foreign key I want it to return the description of this bank, but in many cases, the bank returns me as null

How do I mount the from clause to this? I tried to put another left join only that it returned the same record to me again

    select a.numero,
       a.competencia,
       d.area as setor,
       c.nome as nomeprojeto,
       e.especificacao as elemento,
       e.codigo as codigoelemento,
       f.descricao as rubr,
       f.rubrica as codigorubrica,
       a.valorestimado,
       a.observacao,
       a.fonterecurso codigofonte,
       g.descricao fonte,
       a.competenciadespesa,
       h.nome as credor ,
       h.documento as doccredor,
       h.tipopessoa as tipocredor,
       h.banco as banco,
       h.contacorrente as contacorrente,
       h.agencia as agencia,
       a.processo,
       a.finalidade as objetoSad,
       a.datainclusao,
       a.datamod
from  sad a left join credor h on a.credor = h.codigo,
      cronograma b,
      projeto c,
      organograma d,
      elementodespesa e,
      rubrica f,
      fonterecurso g
where a.cronograma = b.codigo
  and b.projeto  = c.codigo
  and c.organograma = d.codigo
  and b.rubrica = f.codigo
  and f.elementodespesa = e.codigo
  and a.fonterecurso = g.codigo
  and a.codigo = 1954

mysql join

asked by anonymous 11.11.2015 / 15:42

1 answer

"No such file or directory" error but the file is stored in the database Uncaught Error SyntaxError: Unexpected token = [closed]

score 3 · Accepted Answer

I made some modifications to your code and based on this I advise you to review them, as you use them can generate many inconsistencies, such as repeated records and delay in returning the data. The use of "Left Join" prevails the field on the left, so whenever you want to use more than one table, it is more advisable to insert the name of the tables in the "from". Another important issue for your select to perform more is the order of the types of fields in the select. I suggest following this order: "Integer, Date, Time, Numeric, String" regardless of the order of the tables in "Left". Add the field in question that you want to get in the "select" and test the code below and then report if the result is expected.

select a.numero,
       a.competencia,
       d.area as setor,
       c.nome as nomeprojeto,
       e.especificacao as elemento,
       e.codigo as codigoelemento,
       f.descricao as rubr,
       f.rubrica as codigorubrica,
       a.valorestimado,
       a.observacao,
       a.fonterecurso codigofonte,
       g.descricao fonte,
       a.competenciadespesa,
       h.nome as credor,
       h.documento as doccredor,
       h.tipopessoa as tipocredor,
       h.banco as banco,
       h.contacorrente as contacorrente,
       h.agencia as agencia,
       a.processo,
       a.finalidade as objetoSad,
       a.datainclusao,
       a.datamod
from sad a
where (a.codigo = 1954)
left join credor h on (a.credor = h.codigo)
left join cronograma b on (a.cronograma = b.codigo)
left join projeto c on (b.projeto = c.codigo)
left join organograma d on (c.organograma = d.codigo)
left join rubrica f on (b.rubrica = f.codigo)
left join elementodespesa e on (f.elementodespesa = e.codigo)
left join fonterecurso g on (a.fonterecurso = g.codigo)

Note: For a faster return on your "select", I advise you to also study the order of conditions in the where clause, the order is always most likely least improbable.