mySQL Left Join on multiple tables with conditions
- by Jarrod
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.
