CODEWITHSUNDEEP
mysqlsqlsql-server
sausman
sausman

Reputation: 253

Is this possible w/ SQL?

I'm working with a SQL database for a Skills Matrix application and I need a query to return a list of employees that are qualified for a given position. Here's a brief overview of the relevant relationships:

Is there a efficient way to return a list of employees that have the qualifications to meet a certain position's requirements?

EDIT

employees
- id
- name

positions
- id
- title

skills
- id
- name

requirements
- position_id
- skill_id

qualifications
- employee_id
- skill_id

Upvotes: 1

Views: 157

Answers (3)

Markus Jarderot
Markus Jarderot

Reputation: 89241

SELECT *
FROM employees e
WHERE NOT EXISTS (
    SELECT *
    FROM requirements r
    WHERE r.position_id = 1234
    AND NOT EXISTS (
        SELECT *
        FROM qualifications q
        WHERE q.skill_id = r.skill_id
        AND q.employee_id = e.employee_id
    )
)

It will find all employees such that there are no requirement that is not filled by the employee's qualifications.

The use of nested NOT EXISTS is even mentioned in the MySQL Reference Maual

Upvotes: 2

Andrew Savinykh
Andrew Savinykh

Reputation: 26349

Yes, to achieve what you want you need, given a Position to return only those Employees, that have qualifications that collectively include all the skills the Position requires. If you retrieve these Employees, this list it will represent the list of the customers you are after.

Update Since I do not have any experience with mysql, my reply only deals with sql server. Try something like this: 

select 
    qualifications.employee_id 
from 
    qualifications
join 
    requirements 
on 
    requirements.skill_id = qualifications.skill_id
where 
    requirements.position_id = 1234
group by 
    qualifications.employee_id
having
    count(qualifications.skill_id) = (select count(distinct skill_id) from requirements where requirements.position_id = 1234)

Create an index on each of

  • employees.id
  • qualifications.employee_id
  • qualifications.skill_id
  • requirements.skill_id
  • requirements.position_id

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

Sure, it's possible. I count seven tables there: Employee, Qualifation, EmployeeQualifications, Position, Requirement, PositionRequirements, and RequirementQualifications. If your Qualifications and Requirements will always be 1:1 (ie, they are the same thing), you can do it more simply (5 tables) like this: Employee, Position, Skill, EmployeeSkills, PositionSkills.

Once you have the tables defined, the trick to building the query is to first look for any positions for which an employee is not qualified, and then do an exclusion join with that result set back to the position table to get your results.

Upvotes: 2

Related Questions