need to put an extra left Join in this query

Asked

Viewed 179 times

0

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

How do I set up the from clause for that? I tried to add one left join only he returned me the same record over and over 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
  • 1

    There are some inconsistencies in this SQL of yours. The first, you would have to add each left Join to your place, each row, a left, something else... based on what you are joining the chart tables, elementexpense, fonterecurso, which field?

1 answer

3


I made some modifications to your code and based on this I advise you to review them, the way you use can generate many inconsistencies, such as repeated records and delay in the return of 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 instead of inserting the table name in "from". Another important issue for your select to perform better is the order of field types in "select". I suggest following this order: "Integer, Date, Time, Numeric, String" independent of the order of the tables in the "Left". Add the field you want to get in "select" and test the code below and then tell us if the result is as 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 the conditions in clause "Where", the order is always from most likely to least unlikely.

Browser other questions tagged mysql join

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