Sum with Left Outer Join causing missing records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sum with Left Outer Join causing missing records

Author

Topic

rtown
Yak Posting Veteran

53 Posts

Posted - 2013-10-23 : 11:33:00

Hello all,
I have a command which uses a left outer joint of a table, which should leave out records where the table on the left is NULL. But, within the select I have to SUM some records. I believe this is causing the command to ignore the left join in favour of the sum, therefore if there are null values in the left table, it simply does not include this record in the results as there is nothing to SUM.

Is there a way around this? I have tried a sub-select but had no luck.


SELECT Jobs.ID, Jobs.Job, Jobs.Customer, Jobs.JobName, Jobs.DueDate, Jobs.DrawnBy, Jobs.NDT, Jobs.Inspection, Jobs.QualityControl, Jobs.EstDetail, Jobs.Active,
ISNULL(SUM(CONVERT(MONEY, LTRIM(Timeclock.REGHR))),0) as CAD, ISNULL(SUM(CONVERT(MONEY, LTRIM(Timeclock.OVERT1))),0) as CADOT, LTRIM(Timeclock.JOBID), LTRIM(Timeclock.EMPLOYEEID)
FROM Jobs LEFT OUTER JOIN Timeclock ON Jobs.Job = LTRIM(Timeclock.JOBID) 
WHERE Jobs.Active='True' AND Jobs.DrawnBy='Bob' AND LTRIM(Timeclock.EMPLOYEEID)='00004' 
GROUP BY Jobs.ID, Jobs.Job, Jobs.Customer, Jobs.JobName, Jobs.DueDate, Jobs.DrawnBy, Jobs.NDT, Jobs.Inspection, Jobs.QualityControl, Jobs.EstDetail, Jobs.Active, Timeclock.JOBID, Timeclock.EMPLOYEEID 
ORDER BY CONVERT(INT, Jobs.Job) DESC

Any advice appreciated.
Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:06:36

You're summing OVERT1 right which obviously wont have any values where there are no matches in Timeclock. In such cases what do you want to sum on? Ideally it should be ignored isnt it? Similary for REGHR too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rtown
Yak Posting Veteran

53 Posts

Posted - 2013-10-23 : 13:12:10

quote:
Originally posted by visakh16

You're summing OVERT1 right which obviously wont have any values where there are no matches in Timeclock. In such cases what do you want to sum on? Ideally it should be ignored isnt it? Similary for REGHR too

I guess that's the problem. I used left join because where there are no matches in Timeclock I still want the display the record from Jobs. But it seems like when there are no values to SUM, it leave out the records from Jobs altogether.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:23:52

I think I got your issue. If your attempt is to get all rows from Jobs without considering match on timeclock then what you need to tweak is this. You dont need to do anything on SUM


SELECT Jobs.ID, Jobs.Job, Jobs.Customer, Jobs.JobName, Jobs.DueDate, Jobs.DrawnBy, Jobs.NDT, Jobs.Inspection, Jobs.QualityControl, Jobs.EstDetail, Jobs.Active,
ISNULL(SUM(CONVERT(MONEY, LTRIM(Timeclock.REGHR))),0) as CAD, ISNULL(SUM(CONVERT(MONEY, LTRIM(Timeclock.OVERT1))),0) as CADOT, LTRIM(Timeclock.JOBID), LTRIM(Timeclock.EMPLOYEEID)
FROM Jobs LEFT OUTER JOIN Timeclock ON Jobs.Job = LTRIM(Timeclock.JOBID) AND LTRIM(Timeclock.EMPLOYEEID)='00004'
WHERE Jobs.Active='True' AND Jobs.DrawnBy='Bob' AND LTRIM(Timeclock.EMPLOYEEID)='00004'
GROUP BY Jobs.ID, Jobs.Job, Jobs.Customer, Jobs.JobName, Jobs.DueDate, Jobs.DrawnBy, Jobs.NDT, Jobs.Inspection, Jobs.QualityControl, Jobs.EstDetail, Jobs.Active, Timeclock.JOBID, Timeclock.EMPLOYEEID 
ORDER BY CONVERT(INT, Jobs.Job) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:24:42

And here's the explanation on the reason

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rtown
Yak Posting Veteran

53 Posts

Posted - 2013-10-23 : 14:34:54

quote:
Originally posted by visakh16

I think I got your issue. If your attempt is to get all rows from Jobs without considering match on timeclock then what you need to tweak is this. You dont need to do anything on SUM

This works perfectly. I see now what was happening now.
Thank you very much for the time visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 01:32:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources