syntax - SQL Left Join not returning "0" or null -

i trying count of open positions, "paposition" table holds available positions, "employee" table holds employees in position. when run statement below receive

department     job code     count(*)  10             2            1  10             1            1 

but i'm expecting this; because there no employees in job code "3'

department     job code     count(*)  10             3            0  10             2            1  10             1            1 

how can count job code 3 show "0" or "null" acceptable

select pos.department, pos.job_code, count(*) paposition pos left join employee emp on (pos.department = emp.department , pos.job_code = emp.job_code) pos.job_code in ('1', '2', '3') , pos.end_date = '01-jan-1700' , (emp.emp_status 'a%' or emp.emp_status 'l%') , emp.department = '0010' group pos.department, pos.job_code order pos.department, pos.job_code 

the problem undoing left outer join including columns emp in where clause.

you need move conditions on clause:

select pos.department, pos.job_code, count(*) paposition pos left join      employee emp      on (pos.department = emp.department ,         pos.job_code = emp.job_code ,         (emp.emp_status 'a%' or emp.emp_status 'l%') , emp.department = '0010' pos.job_code in ('1', '2', '3') ,       pos.end_date = '01-jan-1700' , group pos.department, pos.job_code order pos.department, pos.job_code; 

(or, alternatively, check null being valid value in where clause emp.emp_status , emp.department.)