How to get number of job vacancies in table job position using the column position in table employee in mysql query?

user_11930825 0
Viewed 36

I have table for Job position and Employees

tbl_job_position:

job_id, job_description, job_quantity(number of max vacant)

1     Programmer        3
2     Accountant        2
3     Driver            3
4     HR Officer        2

tbl_employee:

emp_id, emp_pos_id 
1       1
2       1
3       2
4       3
5       3
6       3

I would like to create a report resulting to this:

job_position, no_of_active_emp, no_of_vacancies
   Programmer        2                 1
   Accountant        1                 1
   Driver            3                 0
   HR Officer        0                 2

How can I query this in mysql? Any help would be appreciated, Thank you.

mysql
1 Answers

Query -

select job_description, cnt as no_of_active_emp, 
job_quantity - e.cnt as no_of_vacancies
from tbl_job_position p, 
(select e.emp_pos_id, count(*) cnt
from tbl_employee e
group by e.emp_pos_id) e
where p.job_id = e.emp_pos_id

DB fiddle here

To show job positions that is not assigned to employees, we select all from job_position table and select only matching records from vacancies tables that is a left join.

select job_description, COALESCE(cnt,0) as no_of_active_emp,
job_quantity - COALESCE(e.cnt,0) as no_of_vacancies
from tbl_job_position p left join
(select e.emp_pos_id, count(*) cnt
from tbl_employee e
group by e.emp_pos_id) e
on p.job_id = e.emp_pos_id

DB fiddle here

user_139153130
Related