left join with sum and group by

[Andreas Schmidt]

Hi there,
meanwhile I spent two days trying to get my view right, but well, seems I'm just a rookie.
I have two tables from which I want to create a view.

Table a contains calendar working days, e.g.

date weekday workinghours
--------------------------------
2010-08-02 Mo 8
2010-08-03 Tue 8
2010-08-04 Wed 8
...

Table b contains activities entered by employees, e.g.

date employee project hours
----------------------------------
2010-08-02 Miller Project1 5
2010-08-02 Miller Project2 2
2010-08-02 Smith Project1 10
2010-08-04 Miller xxx 8
2010-08-04 Smith yyy 6

Now I wanted to create a view, which looks like this:

date employee workinghours hours diff
2010-08-02 Miller 8 7 -1
2010-08-02 Smith 8 10 2
2010-08-03 Miller 8 0 -8 !
2010-08-03 Smith 8 0 -8 !
2010-08-04 Miller 8 8 0
2010-08-04 Smith 8 6 -2

This means I want to see ALL working dates with aggregated hours and the difference to the working-hours. My problem is that the working dates of
employees do not appear if they haven't entered any value for that day.
For better understanding I marked the missing rows with "!".
My understanding of the left join was, that exactly therefor it is designed.

This is the select statement I used for the view:

select a.date,a.weekday,a.workinghours,
b.employee,sum(b.hours), (sum(b.hours)-a.workinghours)
from a left join b
on a.date = b.date
group by a.date,b.employee

We use mysql 5.1.42.

Well, I have no idea left. Can anybody help me?
Many thanks!

regards
Andreas