I have a problem making a LEFT JOIN in Crystal Reports. From what I've researched, it's a classic SQL problem when using WHERE criteria by crossing LEFT JOIN . I tried to solve it anyway, but I could not.
There are 4 related tables: [OrcHDR] [Production Order] [ItemNote] [Fiscal Notes]
I am interested in a report with all OP requests, INCLUDING those that have not yet been invoiced, with the exception of some that are specified
Here is the SQL:
SELECT
"OrcHdr"."SPreco",
"OrcHdr"."SCustosMat",
"OrcHdr"."SCustosMO",
"OrcHdr"."SCustosTerc",
"OrcHdr"."SCustosImpostos",
"OrcHdr"."SCustosComissoes",
"OrcHdr"."SCustosFin",
"OrcHdr"."SCustosVenOutros",
"OrcHdr"."NomeAgencia",
"OrdensProducao"."NumOrdem",
"OrdensProducao"."Descricao",
"OrdensProducao"."NomeCliente",
"OrdensProducao"."TipoProduto",
"ItemNota"."Fatura",
"ItemNota"."Devolucao",
"OrdensProducao"."DtEmissao",
"ItemNota"."ValorTotal",
"NotasFiscais"."NaturezaOperacao",
"NotasFiscais"."Situacao",
"OrcHdr"."NumOrcamento",
"ItemNota"."NumOrdem"
FROM
(("TABELA"."dbo"."OrcHdr" "OrcHdr"
INNER JOIN
"TABELA"."dbo"."OrdensProducao" "OrdensProducao" ON
"OrcHdr"."NumOrcamento"="OrdensProducao"."NumOrdem")
LEFT OUTER JOIN
"TABELA"."dbo"."ItemNota" "ItemNota" ON "OrdensProducao"."NumOrdem"="ItemNota"."NumOrdem")
INNER JOIN
"TABELA"."dbo"."NotasFiscais" "NotasFiscais" ON "ItemNota"."ObjID_Nota"="NotasFiscais"."ObjID"
WHERE
("ItemNota"."Devolucao" IS NULL OR "ItemNota"."Devolucao"<>'D') AND ("ItemNota"."Fatura" IS NULL OR "ItemNota"."Fatura"='F') AND
("NotasFiscais"."Situacao" IS NULL OR "NotasFiscais"."Situacao"='N') AND ("NotasFiscais"."NaturezaOperacao" IS NULL OR NOT ("NotasFiscais"."NaturezaOperacao" LIKE 'doa%' OR "NotasFiscais"."NaturezaOperacao" LIKE 'reme%'))
ORDER BY "OrcHdr"."NumOrcamento"
What is really happening is that I only get the OPs that have already been invoiced, that is, they are present in the tables [ItemNote] and [PartyNotes].
What am I doing wrong?