Hi

I'm really struggling with this query. I have 4 tables (http://oberto.co.nz/db-sql.png):

Invoice_Payement, Invoice, Client and Calendar. I'm trying to create a report by summing up the 'paid_amount' col, in Invoice_Payment, by month/year.

• The query needs to include all months, even those with no data
• There query needs the condition (Invoice table): registered_id = [id]

I have tried with the below query, which works, but falls short when 'paid_date' does not have any records for a month. The outcome is that month does not show in the results

I added a Calendar table to resolved this but not sure how to left join to it.

SELECT 
   MONTHNAME(Invoice_Payments.date_paid) as month, 
   SUM(Invoice_Payments.paid_amount) AS total
FROM Invoice, Client, Invoice_Payments
WHERE Client.registered_id = 1
AND Client.id = Invoice.client_id
And Invoice.id = Invoice_Payments.invoice_id
AND date_paid IS NOT NULL
GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid)

Please see the above link for a basic ERD diagram of my scenario.

Thanks for reading. I've posted this Q before but I think I worded it badly.