query does not return all expected rows

Question

SELECT p.INVOICE_PAYMENT_ID,i.INVOICE_ID,i.INVOICE_NUMBER,i.CLINIC_ID,
    i.INVOICE_DATE,i.PAYMENT_RECEIVED_DATE, i.AMOUNT,
    i.CURRENT_CHARGE,i.AMOUNT_RECEIVED,i.MONTH,i.YEAR,i.COMPANY_ID,
    i.COMPANY_NAME,c.NAME,p.NOTE,i.NOTES
FROM test1 i 
LEFT JOIN test2 c ON (i.CLINIC_ID=c.CLINIC_ID)
LEFT JOIN test3 p ON p.INVOICE_ID=i.INVOICE_ID  
WHERE i.MONTH=11 AND i.YEAR=2011                     
    AND  p.INVOICE_PAYMENT_ID IN (
        SELECT MAX(INVOICE_PAYMENT_ID) 
        FROM test3 WHERE INVOICE_ID=i.INVOICE_ID 
    )

If the inner query has no value then it does not return all the corresponding rows in test3.

How do I fix this?

Answer 1

Move your inner query from where clause to join condition:

SELECT p.INVOICE_PAYMENT_ID,i.INVOICE_ID,i.INVOICE_NUMBER,i.CLINIC_ID,
    i.INVOICE_DATE,i.PAYMENT_RECEIVED_DATE, i.AMOUNT,
    i.CURRENT_CHARGE,i.AMOUNT_RECEIVED,i.MONTH,i.YEAR,i.COMPANY_ID,
    i.COMPANY_NAME,c.NAME,p.NOTE,i.NOTES
FROM test1 i 
LEFT JOIN test2 c ON (i.CLINIC_ID=c.CLINIC_ID)
LEFT JOIN test3 p ON p.INVOICE_ID=i.INVOICE_ID and p.INVOICE_PAYMENT_ID = (
        SELECT MAX(INVOICE_PAYMENT_ID) 
        FROM test3 WHERE INVOICE_ID=i.INVOICE_ID)
WHERE i.MONTH=11 AND i.YEAR=2011