Query showing AP invoices and Check numbers to include AP Invoices not paid yet

[former_member268870]

Experts,

I have a query that shows AP Invoices and the linked Check numbers. But it does not show the AP Invoices that was not yet paid.... I did several joins from the OPCH and OVPM tables but not one will give also show the unpaid AP Invoices....

Your help would be greatly appreciated,

Marli

SELECT T0.[CardCode], T0.[CardName], T0.[DocNum] AS 'AP Inv #', T0.[DocType], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal], T4.[DueDate], T4.[CheckNum], T4.[CheckSum] 

FROM OPCH T0  

INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] 

INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] 

LEFT OUTER JOIN OVPM T3 ON T0.[ReceiptNum] = T3.[DocEntry] 

INNER JOIN VPM1 T4 ON T3.[DocEntry] = T4.[DocNum] 

WHERE T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1] AND  T0.[CardCode] = 'V00232'

[Johan_Hakkesteegt · Accepted solution]

Hi Marli,

Any INNER JOIN will limit the result, even ones that are not joined directly to the primary table. Please try this:

SELECT T0.[CardCode]
     , T0.[CardName]
     , T0.[DocNum] AS 'AP Inv #'
     , T0.[DocType]
     , T0.[DocStatus]
     , T0.[DocDate]
     , T0.[DocDueDate]
     , T1.[ItemCode]
     , T1.[Dscription]
     , T1.[Quantity]
     , T1.[Price]
     , T1.[LineTotal]
     , T4.[DueDate]
     , T4.[CheckNum]
     , T4.[CheckSum]
FROM OPCH T0  
     INNER JOIN PCH1 T1 ON T0.[DocEntry]= T1.[DocEntry]
     INNER JOIN OCRD T2 ON T0.[CardCode]= T2.[CardCode]
     LEFT OUTER JOIN OVPM T3 ON T0.[ReceiptNum] = T3.[DocEntry]
     LEFT OUTER JOIN VPM1 T4 ON T3.[DocEntry] = T4.[DocNum]
WHERE T0.[DocDate]>=[%0]
  AND T0.[DocDate]<=[%1]
  AND T0.[CardCode]='V00232'

Regards,

Johan