mySQL Left Join on multiple tables with conditions

Question

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.

Note: the calendar table simply lists every day of the year. It contains a single col call date_field

Database: http://oberto.co.nz/jInvoice.txt

Expected output: my current query, below, will return something like:

month       total
August      5
September   0
October  196
November  205
December  214
January  229

Notice how September isn't returned? It's because table Invoice_Payment has no date_due record

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.

Answer 1

It sounds like you are trying to find values for all dates within a range regardless of whether there is a value or not. Supposing we have a Calendar table structured like so:

Create Table Calendar
(
    [Date] not null Primary Key
)

Your query might look like so (where X and Y represent the start and end date of the range in which you are investigating):

Select Year(C.Date), MonthName(C.Date) As Month
    , Coalesce(Sum(IP.paid_amount),0) As Total
From Calendar As C
    Left Join (Invoice As I
        Join Client As C1
            On C1.id = I.client_id
                And C.registered_id = 1
        Join Invoice_Payments As IP
            On IP.Invoice_id = I.Id)
        On IP.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)

Technically, the above query should do the trick. However, another alternative is to use a derived table about which you inquired in the comments:

Select Year(C.Date), MonthName(C.Date) As Month
    , Coalesce(Sum(Z.paid_amount),0) As Total
From Calendar As C
    Left Join   (
                Select IP.date_paid, IP.paid_amount
                From Invoice As I
                        Join Client As C1
                            On C1.id = I.client_id
                                And C.registered_id = 1
                        Join Invoice_Payments As IP
                            On IP.Invoice_id = I.Id
                ) As Z
        On Z.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)