Add WHERE clause to LEFT JOIN query
OK, I decided to handle my sickdays / holidays query by doing two sepearte queries. For my sickday query, I want to list ALL employees, and then calculate the number of sick days they have remaining to them, by subtracting a count of days taken from a days allowed field. So I have this....
SELECT e.EmployeeID, COUNT(s.SickDayID) AS DaysUsed,
IFNULL(e.SickDaysAllowed - COUNT(s.SickDayID), e.SickDaysAllowed) AS DaysRemaining
FROM employees e
LEFT OUTER JOIN sickdays s ON s.SickEmployee=e.EmployeeID
GROUP BY e.EmployeeID
Which works perfectly, I get a column of all employees, a colunm of the count of all sickdays they have taken, and a column of the number of days they have left to them (which is the calculation allowed-taken, or days allowed if the calculation comes up null).
However, I realized I need to modify they query to display results for the current year only...sick days refresh every calendar year, so I modified the query as follows....
SELECT e.EmployeeID, COUNT(s.SickDayID) AS DaysUsed,
IFNULL(e.SickDaysAllowed - COUNT(s.SickDayID), e.SickDaysAllowed) AS DaysRemaining
FROM employees e
LEFT OUTER JOIN sickdays s ON s.SickEmployee=e.EmployeeID
WHERE YEAR(s.SickDate) = YEAR(CURRENT_DATE)
GROUP BY e.EmployeeID
This kind of works, however it doesn't return all employees, it only returns employees that have sickdays taken in the current year.
I need to modify this to still return all employees.
Any ideas?
Thanks,
Scott