mySQL Left Join on multiple tables
- by Jarrod
Hi
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_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)
Please see the above link for a basic ERD diagram of my scenario.
Thanks for reading.
I've posted this Q before but I think I worded it badly.
