problem adding a where clause to a T-sql LEFT OUTER JOIN query

Question

SELECT TOP (100) PERCENT dbo.EmployeeInfo.id, MIN(dbo.EmployeeInfo.EmpNo) AS EmpNo, 
            SUM(dbo.LeaveApplications.DaysAuthorised) AS DaysTaken
FROM  dbo.EmployeeInfo LEFT OUTER JOIN dbo.LeaveApplications ON 
           dbo.EmployeeInfo.id = dbo.LeaveApplications.EmployeeID
WHERE (YEAR(dbo.LeaveApplications.ApplicationDate) = YEAR(GETDATE()))
GROUP BY dbo.EmployeeInfo.id, dbo.EmployeeMaster.EmpNo
ORDER BY DaysTaken DESC 

The basic functionality i want is to retrieve all records in table dbo.EmployeeInfo irrespective of whether a corresponding record exists in table dbo.LeaveApplications. If a row in EmployeeInfo has no related row in LeaveApplications, i want to return its SUM(dbo.LeaveApplications.DaysAuthorised) AS DaysTaken column as NULL or may be even put a 0.

With the above query, if i remove the where condition, am able to achieve what i want, but problem is i also want to return related rows from LeaveApplication only if ApplicationDate is in the current year.

Now with the where condition added, am only able to get rows from EmployeeInfo only if they have corresponding rows in LeaveApplications yet i just wanted rows all in EmployeeInfo

Answer 1

You can add a test for NULL in your WHERE clause, this can cause your SUM to return NULL as well for rows that do not exist on the LeaveApplications table:

SELECT TOP (100) PERCENT dbo.EmployeeInfo.id, MIN(dbo.EmployeeInfo.EmpNo) AS EmpNo, 
            SUM(dbo.LeaveApplications.DaysAuthorised) AS DaysTaken
FROM  dbo.EmployeeInfo LEFT OUTER JOIN dbo.LeaveApplications ON 
           dbo.EmployeeInfo.id = dbo.LeaveApplications.EmployeeID
WHERE (YEAR(dbo.LeaveApplications.ApplicationDate) = YEAR(GETDATE()))
      OR dbo.LeaveApplications.ApplicationDate IS NULL
GROUP BY dbo.EmployeeInfo.id, dbo.EmployeeMaster.EmpNo
ORDER BY DaysTaken DESC