Sunday, December 02, 2007

outer join and group by clause

Nowadays don't get a lot of chance to be hands-on. The following did not fetch the outer join rows

select ordr, grp, count(ser_id) as [Sev4 Created]
from #myHPSDGrps left outer join dbo.ServiceCallView
on AssignedToWorkgroup = grp
where
[Open Date&Time] >= @begin and
[Open Date&Time] < @end and
Severity = 'Severity 4'
group by ordr, grp
order by ordr

but one below does, moved all the where clause to outer join condition

select ordr, grp, count(ser_id) as [Sev4 Created]
from #myHPSDGrps left outer join dbo.ServiceCallView
on AssignedToWorkgroup = grp and
[Open Date&Time] >= @begin and
[Open Date&Time] < @end and
Severity = 'Severity 4'
group by ordr, grp
order by ordr

Didn't have much time to analyze :0

No comments: