Search code examples
mysqldatabasejoincountsql-in

Unable to make a suitable query

I'm trying to make a database. It has following subset of tables

  1. jobs(job_id, description)
  2. skills (skill_id, skills)
  3. jobs_and_skills (job_id, skill_id)
  4. applicant_skill (applicant_id , skills_id)

My query must be : Find applicants who have skills required by a given jobs id

I did something like this using a series of subqueries

select DISTINCT applicant_id
from applicant_skill
where  skill_id in
(select DISTINCT skill_id
from jobs_and_skill
where  job_id in (select job_id
from jobs
where description ="research"));

But here if I have a job that needs skills say A,B,C and I have candidate which has skill A only it will return that candidate also. Is there a way to find candidate that has all skills A,B,C and not return candidates with just a subset of required skills

Solution

  • Use JOIN rather than IN. Then you can join with a subquery that counts the number of skills a job requires, and compare that with the count of skills the applicant has in common.

    SELECT as.applicant_id
FROM applicant_skill AS as
JOIN jobs_and_skills AS js ON js.skill_id = as.skill_id
JOIN jobs AS j ON j.job_id = js.job_id
JOIN (
    SELECT job_id, COUNT(*) AS skill_count
    GROUP BY job_id
) AS jsc ON j.job_id = jsc.job_id
WHERE j.description = "research"
GROUP BY as.applicant_id
HAVING COUNT(*) = jsc.skill_count