mySQL Left Join on multiple tables

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_Payment开发者_如何学编程s.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.

Database: http://oberto.co.nz/ Expected output: my current query, below, will return something like:

month       total
August      5
September   0
October  196
November  205
December  214
January  229

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

---

Updated :P, Will this work?

SELECT YEAR(calendar.date_field) as year,
 MONTHNAME(calendar.date_field) as month,
 SUM(Invoice_Payments.paid_amount) AS total 
FROM calendar
 left outer join invoice_Payments on calendar.date_field = invoice_Payments.date_paid
 left outer join Invoice on invoice_payments.invoice_id = invoice.invoice_id
 left outer join (select * from client where registered_id = 1) as c on c.id = Invoice.client_id
GROUP BY YEAR(calendar.date_field), MONTH(calendar.date_field)

---

The problem is obviously that you are not doing a left join so a solution could be to rewrite the query to use a the join syntax where you can specify that you want a left join. Something like

Select * from invoice left join client on client.id=invoice.client_id left join

And so on

---

This can also work ...

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