problem adding a where clause to a T-sql LEFT OUTER JOIN query
- by Nickson
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
