Problems with LEFT JOIN

Asked

Viewed 563 times

3

I’ve been racking my brain for a few days with SQL on something that should be very simple, but I can’t fix it.

I want to get all data from the table [Numorder] including those not in the table [Itemnotas] with the SQL code below:

SELECT OrdensProducao.NumOrdem AS 'OP'
   ,OrdensProducao.NomeCliente AS 'Cliente'
   ,OrdensProducao.Descricao AS 'Descrição' 
   ,OrdensProducao.TipoProduto AS 'Produto'
   ,NomeAgencia AS 'Agência'
   ,OrdensProducao.DtEmissao
   ,SPreco AS 'Valor Fechado'
   ,SCustosComissoes AS 'Comissão'
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros) AS 'Contr Marginal'
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros - SCustosMO) AS 'Lucro'
   ,SUM(ItemNota.ValorTotal) AS 'Faturado'
FROM
    ((OrdensProducao INNER JOIN OrcHdr ON OrdensProducao.NumOrdem = OrcHdr.NumOrcamento)
     LEFT OUTER JOIN ItemNota ON OrcHdr.NumOrcamento = ItemNota.NumOrdem)
     INNER JOIN NotasFiscais ON ItemNota.ObjID_Nota=NotasFiscais.ObjID

WHERE
    (NotasFiscais.NaturezaOperacao IS NULL OR (NOT NotasFiscais.NaturezaOperacao LIKE 'doa*' OR NotasFiscais.NaturezaOperacao LIKE 'reme*')) AND
    (ItemNota.Devolucao IS NULL OR ItemNota.Devolucao<> 'D') AND
    (NotasFiscais.Situacao IS NULL OR NotasFiscais.Situacao = 'N') AND
    (ItemNota.Fatura IS NULL OR ItemNota.Fatura = 'F')

GROUP BY
    OrdensProducao.NumOrdem
   ,OrdensProducao.NomeCliente
   ,OrdensProducao.Descricao 
   ,OrdensProducao.TipoProduto
   ,NomeAgencia
   ,OrdensProducao.DtEmissao
   ,SPreco
   ,SCustosComissoes
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros)
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros - SCustosMO)
ORDER BY
    OrdensProducao.NumOrdem

But I only get the equivalent data from the two tables! If I remove the [Notasficais] table, it works perfectly, but I need the 'WHERE' dependent on it. I’m pretty sure it’s a relationship thing, but I can’t fix it.

sql sql-server join

  • It would not be the case to use LEFT also with Notasfiscais instead of INNER?

  • I’ve tried it too. But it’s not necessary because all the records in Notasfiscais exist in Itensnote

  • 1

    Yes, but your query will only bring Itensnote whose NF meets WHERE conditions.

  • 1

    Change the order: first the INNER JOINS, then the OUTERS. Never an OUTER and then an INNER. You break the logic of your OUTER.

  • @rodrigogq, could you help me assemble this line in the correct order, please? I’m starting with SQL yet..

3 answers

1


The table ItemNota has two IDs (Don’t ask me why, I didn’t create the comic) and the ID that connects with the table Notas is the ObjID_Notas.

About relationships, I did the following:

FROM
    OrdensProducao 
    INNER JOIN OrcHdr ON OrdensProducao.NumOrdem = OrcHdr.NumOrcamento 
    LEFT OUTER JOIN (ItemNota INNER JOIN NotasFiscais ON ItemNota.ObjID_Nota = NotasFiscais.ObjID) ON OrdensProducao.NumOrcamento = ItemNota.NumOrdem

I don’t know if it’s the right way, but I did it this way.

  • 1

    That is correct, yes! And you were able to do whatever you were, first the INNER JOIN, because the ones in parentheses go first, and then the OUTER!

  • Cassio, if there is any other answer that answered your question, mark it as right. Otherwise, mark your own answer as right. Hug!

1

It seems the error lies in the equality of Left Join:

SELECT OrdensProducao.NumOrdem AS 'OP'
   ,OrdensProducao.NomeCliente AS 'Cliente'
   ,OrdensProducao.Descricao AS 'Descrição' 
   ,OrdensProducao.TipoProduto AS 'Produto'
   ,NomeAgencia AS 'Agência'
   ,OrdensProducao.DtEmissao
   ,SPreco AS 'Valor Fechado'
   ,SCustosComissoes AS 'Comissão'
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros) AS 'Contr Marginal'
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros - SCustosMO) AS 'Lucro'
   ,SUM(ItemNota.ValorTotal) AS 'Faturado'
FROM
    ((OrdensProducao INNER JOIN OrcHdr ON OrdensProducao.NumOrdem = OrcHdr.NumOrcamento)
     LEFT OUTER JOIN ItemNota ON **OrdensProducao.NumOrdem** = ItemNota.NumOrdem)
     INNER JOIN NotasFiscais ON ItemNota.ObjID_Nota=NotasFiscais.ObjID

WHERE
    (NotasFiscais.NaturezaOperacao IS NULL OR (NOT NotasFiscais.NaturezaOperacao LIKE 'doa*' OR NotasFiscais.NaturezaOperacao LIKE 'reme*')) AND
    (ItemNota.Devolucao IS NULL OR ItemNota.Devolucao<> 'D') AND
    (NotasFiscais.Situacao IS NULL OR NotasFiscais.Situacao = 'N') AND
    (ItemNota.Fatura IS NULL OR ItemNota.Fatura = 'F')

GROUP BY
    OrdensProducao.NumOrdem
   ,OrdensProducao.NomeCliente
   ,OrdensProducao.Descricao 
   ,OrdensProducao.TipoProduto
   ,NomeAgencia
   ,OrdensProducao.DtEmissao
   ,SPreco
   ,SCustosComissoes
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros)
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros - SCustosMO)
ORDER BY
    OrdensProducao.NumOrdem

1

Try to do the following (the commented part is where I changed only):

SELECT OrdensProducao.NumOrdem AS 'OP'
   ,OrdensProducao.NomeCliente AS 'Cliente'
   ,OrdensProducao.Descricao AS 'Descrição' 
   ,OrdensProducao.TipoProduto AS 'Produto'
   ,NomeAgencia AS 'Agência'
   ,OrdensProducao.DtEmissao
   ,SPreco AS 'Valor Fechado'
   ,SCustosComissoes AS 'Comissão'
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros) AS 'Contr Marginal'
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros - SCustosMO) AS 'Lucro'
   ,SUM(ItemNota.ValorTotal) AS 'Faturado'

-- esta é o original.. vou mudar um pouco a forma para entender melhor...
-- FROM
--    ((OrdensProducao INNER JOIN OrcHdr ON OrdensProducao.NumOrdem = OrcHdr.NumOrcamento)
--     LEFT OUTER JOIN ItemNota ON OrcHdr.NumOrcamento = ItemNota.NumOrdem)
--     INNER JOIN NotasFiscais ON ItemNota.ObjID_Nota=NotasFiscais.ObjID

FROM
    -- todas as ordens...
    OrdensProducao
INNER JOIN OrcHdr ON
    -- E que estejam associadas com 'OrcHdr'
    OrdensProducao.NumOrdem = OrcHdr.NumOrcamento
LEFT OUTER JOIN NotasFiscais ON
    -- que TALVEZ possuam notas fiscais
    ItemNota.ObjID_Nota=NotasFiscais.ObjID
--
--  Acho que este ponto esta errado né? Não seria algo como ItemNota.NotaID = NotasFiscais.NotaId ?????????
-- LEFT OUTER JOIN ItemNota ON
--    OrcHdr.NumOrcamento = ItemNota.NumOrdem
--

WHERE
    (NotasFiscais.NaturezaOperacao IS NULL OR (NOT NotasFiscais.NaturezaOperacao LIKE 'doa*' OR NotasFiscais.NaturezaOperacao LIKE 'reme*')) AND
    (ItemNota.Devolucao IS NULL OR ItemNota.Devolucao<> 'D') AND
    (NotasFiscais.Situacao IS NULL OR NotasFiscais.Situacao = 'N') AND
    (ItemNota.Fatura IS NULL OR ItemNota.Fatura = 'F')

GROUP BY
    OrdensProducao.NumOrdem
   ,OrdensProducao.NomeCliente
   ,OrdensProducao.Descricao 
   ,OrdensProducao.TipoProduto
   ,NomeAgencia
   ,OrdensProducao.DtEmissao
   ,SPreco
   ,SCustosComissoes
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros)
   ,(SPreco - SCustosComissoes - SCustosMat - SCustosTerc - SCustosImpostos - SCustosFin - SCustosVenOutros - SCustosMO)
ORDER BY
    OrdensProducao.NumOrdem

I think the mistake was in the INNER for LEFT with the IRS bills... Note Items, on the other hand, seems to be misrelating. Make the selection of Nfs first and then you see how you do with the items.

Browser other questions tagged sql sql-server join

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