problem adding a where clause to a T-sql LEFT OUTER JOIN query
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 Leave开发者_开发问答Applications yet i just wanted rows all in EmployeeInfo
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
精彩评论