title: mySQL Left Join on multiple tables with conditions
body: <p>I have a problem. I have 4 tables:</p>
  
  <p>Invoice_Payment, Invoice, Client, and Calendar</p>
  
  <p>Basically, I have the below query, which works well, except, months with no date_due don't return. I.E only months with a date_due will be returned.</p>
  
  <p>Note: the calendar table simply lists every day of the year. It contains a single col call date_field</p>
  
  <p>Database: <a href="http://oberto.co.nz/jInvoice.txt" rel="nofollow">http://oberto.co.nz/jInvoice.txt</a></p>
  
  <p>Expected output: my current query, below, will return something like:</p>
  
  <pre><code>month       total
  August      5
  September   0
  October  196
  November  205
  December  214
  January  229
  </code></pre>
  
  <p>Notice how September isn't returned? It's because table Invoice_Payment has no date_due record</p>
  
  <p>I think I have to use a left join and join the calendar table with something like:
  LEFT JOIN Calendar ON Invoice_Payments.date_paid = Calendar.date_field. But I'm having no luck</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Any help appreciated.</p>