CODEWITHSUNDEEP
sqlsyntax
Bobby Hussey
Bobby Hussey

Reputation: 1

SQL Left Join not returning "0" or null

I am trying to get a count of all open positions, the "paposition" table holds all available positions, the "employee" table holds all employees in that position. When I run the statement below I receive 

Department     Job Code     Count(*)

10             2            1

10             1            1

but I'm expecting this; because there are currently no employees in Job Code "3'

Department     Job Code     Count(*)

10             3            0

10             2            1

10             1            1

How can I get the count for Job Code 3 to show as "0" or "null" is also acceptable

select pos.department, pos.job_code, count(*)
from paposition pos
left join employee emp
on (pos.department = emp.department
and pos.job_code = emp.job_code)
where pos.job_code in ('1', '2', '3')
and pos.end_date = '01-Jan-1700'
and (emp.emp_status like 'A%'
or emp.emp_status like 'L%')
and emp.department = '0010'
group by pos.department, pos.job_code
order by pos.department, pos.job_code

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The problem is that you are undoing the left outer join by including columns from emp in the where clause.

You need to move the conditions to the on clause:

select pos.department, pos.job_code, count(*)
from paposition pos left join
     employee emp
     on (pos.department = emp.department and
        pos.job_code = emp.job_code and
        (emp.emp_status like 'A%' or emp.emp_status like 'L%') and emp.department = '0010'
where pos.job_code in ('1', '2', '3') and
      pos.end_date = '01-Jan-1700' and
group by pos.department, pos.job_code
order by pos.department, pos.job_code;

(Or, alternatively, you could check for NULL being a valid value in the where clause for emp.emp_status and emp.department.)

Upvotes: 3

Related Questions