Convert NULL to Zero After JOIN and Add Values together

[Bob]

I posted this in different forum, but think this forum is more appropriate for the question below. I am creating 4 temp tables a, b, c, and d with month (mon) as a common column. I will always have data in tables a and b,but c could be null. That is, there could be no row in table c for that given month. Table d is created to perform addition on values in a, b, and c.

After joining the databases, rightfully so, Mysql returns NULL for the missing months:

create temporary table d (mon int(2), diff decimal(9,2)) select a.mon as mon, a.amtx + b.amty + c.amtz as diff from (a left join b using (mon) left join c using (mon)) group by a.mon;

select a.mon, a.amtx, b.amty, c.amtz, d.diff from (a left join b using (mon) left join c using (mon) left join d using (mon)) group by a.mon;

Gives the result:

a.mon a.amtx b.amty c.amtz d.diff
1 -10.00 14.00 -5.00 -1.00
2 -14.00 19.00 -3.00 2.00
3 -20.000 30.00 NULL NULL

Now I am trying to convert the NULL's to 0. I have tried coalese(), and ifnull(), but table c does not have NULL amounts, The NULL comes about because of the join. Further, I have table d which adds values in a, b, and, c, and so if there is no value for a given month in table c, then the math also returns a NULL in table d.

What I want is for the math to work even if c.amtz is null, and have zeros instead of the word NULL, so:

a.mon a.amtx b.amty c.amtz d.diff
1 -10.00 14.00 -5.00 -1.00
2 -14.00 19.00 -3.00 2.00
3 -20.000 30.00 0 10.00

So the question is how do you convert NULL to zero upon the left join, there is no cooresdonding row in Table c?


Any help would be appreciated. Thank you.