mysql left join and sum group by

Question

Hi i using this mysql code to generate this data below:

code:

SELECT L.LEAVETYPE, LA.IDLEAVETYPE, LA.IDLEAVETABLE, SUM( LA.NOOFDAYS ) 
FROM LEAVETYPE AS L
LEFT JOIN LEAVEAPPLICATION AS LA ON LA.IDLEAVETYPE = L.IDLEAVETYPETABLE
GROUP BY L.LEAVETYPE

When i add the Where condition it will only show the LeaveType with value. How can i show all the leavetype including the null (Like in the first picture)?

SELECT L.LEAVETYPE, LA.IDLEAVETYPE, LA.IDLEAVETABLE, SUM(LA.NOOFDAYS)
FROM LEAVETYPE AS L
LEFT JOIN LEAVEAPPLICATION AS LA ON LA.IDLEAVETYPE = L.IDLEAVETYPETABLE
WHERE LA.IDSTAFFTABLE='24'
GROUP BY L.LEAVETYPE

Answer 1

Move the idstafftable condition to the join instead of the where:

LEFT JOIN LEAVEAPPLICATION AS LA ON LA.IDLEAVETYPE = L.IDLEAVETYPETABLE AND LA.IDSTAFFTABLE='24'