开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜