MySQL Forums
Forum List  »  Newbie

Add WHERE clause to LEFT JOIN query
Posted by: Scott Harris
Date: May 08, 2005 04:51AM

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

Options: ReplyQuote


Subject
Written By
Posted
Add WHERE clause to LEFT JOIN query
May 08, 2005 04:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.