Right Outer join issue

Question

I've two tables that I want to join and filter data from. I used a stored procedure to do that. My intention is to bring every item from the second table (i.e Department) even if they don't have a matching record in the first table (i.e. Employee) and finally display the count. Here is the segment of the code I used:

select d.deptName, 
case when COUNT(*) is null then '0' else count(*) end AS total 
from Employee e 
right outer join Department d on e.deptID=d.deptID 
WHERE e.Year=@year
and e.Month=@month
group by d.deptName
order by d.deptName

But, it's not displaying what i wanted and failed to figure out the real problem.

score 1 · Accepted Answer · answered Nov 19 '12 at 04:07

When you apply the filter condition through where clause after join, it filters out all the records which doesn't satisfy the filter criteria. Try moving your filter criteria in join condition itself as below:

   select d.deptName, 
         case when COUNT(*) is null then '0' else count(*) end AS total 
   from Employee e 
   right outer join Department d 
      on (e.Year=@year 
          and e.Month=@month
          and e.deptID=d.deptID)
   group by d.deptName
   order by d.deptName

score 0 · Answer 2 · edited Dec 15 '12 at 14:14

0

I think you need to change the code like this

SELECT d.deptName, COUNT(e.deptID) AS total
   FROM Employee e
   RIGHT OUTER JOIN Department d
      ON (e.Year= @year 
          AND e.Month= @month
          AND e.deptID=d.deptID)
   GROUP BY d.deptName
   ORDER BY d.deptName

See the SQL Fiddle for the query : http://sqlfiddle.com/#!3/b1105/17

edited Dec 15 '12 at 14:14

marc_s

732,580
175
1,330
1,459

answered Nov 19 '12 at 04:30

wizzardz

5,664
5
44
66

Right Outer join issue

2 Answers2