MySQL Left Join on multiple tables?

The problem is obviously that you are not doing a left join so a solution could be to rewrite the query to use a the join syntax where you can specify that you want a left join. Something like.

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

Thanks for replying. I get "#1066 - Not unique table/alias: 'C'" – Jarrod Jan 16 at 7:16 @Jarrod - Fixed. I used the alias "C" for both the Calendar table and the Client table.

Have simply changed the alias on the Client table. – Thomas Jan 16 at 18:09 thanks for editing. However, I still get some errors that I cannot resolve: "Unknown column 'I.

Date_paid' in 'on clause'". I've added the following diagram to help understand my issue: oberto.co. Nz/db-sql.

Png – Jarrod Jan 16 at 18:44 @Jarrod - That's a column from your original query. Since you did not use a prefix, I have no way of knowing in which table it is contained. I guessed that it was Invoice table.

However, if it is the Invoice_Payment table just change I. Date_paid to IP. Date_paid.

– Thomas Jan 16 at 21:58 Thanks again. Still not the result I'm after as Only January's data is included. See oberto.co.

Nz/Selection_204.png. I think it's on the right track but it's also missing the condition Client. Registered_id = 1.

This is driving me mental – Jarrod Jan 16 at 22:43.

I would explicitly define all your joins for readability. A simple example of an outer join preserving all rows in the first table, filling any missing columns in the second table with nulls: select column1, from table1 left outer join table2 on table1. Key = table2.

Key Assuming the Calendar table is the one causing you to lose data, here's a shot at your query: SELECT MONTHNAME(Invoice_Payments. Date_paid) as month, SUM(Invoice_Payments. Paid_amount) FROM Invoice JOIN Client ON Client.Id = Invoice.

Client_id AND Client. Registered_id = 1 JOIN Invoice_Payments ON Invoice_Payments. Invoice_id = Invoice.

Id LEFT OUTER JOIN Calendar ON Calendar. Key = #yourothertable#. Key WHERE date_paid IS NOT NULL GROUP BY YEAR(Invoice_Payments.

Date_paid), MONTH(Invoice_Payments. Date_paid) Good luck!

Thanks for replying. I think this is on the right track. But it's the Invoice_Payment table causing me to loose data.

I've updated my question above to show this. If there is not record in the Invoice_Payment table for a particular month, then that month won't show. And that's where I'm stuck – Jarrod Jan 16 at 7:23 you might have figured it out by now, but if you are losing data because of the Invoice_Payment table then you could change that particular join in the statement I wrote to: 'LEFT OUTER JOIN Invoice_Payments' and remove the two lines about the calendar – cag Jan 21 at 5:19.

I've run into a similar issue and solved it by moving the non calendar table conditions out of the WHERE clause and into the join. See my full explanation here: https://gist.github. Com/1137089 In your case you'll need to factor the from table1, table2, table3 into left join table1 on x=y etc...

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.

I've run into a similar issue and solved it by moving the non calendar table conditions out of the WHERE clause and into the join. See my full explanation here: gist.github.com/1137089.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.