mySQL Left Join on multiple tables with conditions

Posted by Jarrod on Stack Overflow See other posts from Stack Overflow or by Jarrod
Published on 2011-01-16T06:04:14Z Indexed on 2011/01/16 6:53 UTC
Read the original article Hit count: 324

Filed under:
|
|

Hi,

I have a problem. I have 4 tables:

Invoice_Payment, Invoice, Client, and Calendar

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.

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

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)

Any help appreciated.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about query