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
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