MySQL where all in

mysql

Solution

If you know the number of qualifications that are required for the job, you can write this query:

SELECT uq.user_id
FROM users_qualification uq JOIN job_qualifications jq
    ON uq.qualification_id = jq.qualification_id
WHERE jq.jobs_id = 1
GROUP BY uq.user_id
HAVING COUNT(*) = {# of qualifications}

To find the number of qualifications, run:

SELECT COUNT(*)
FROM job_qualifications
WHERE jq.jobs_id = 1

Problem

I have five tables in my database, users, jobs, qualifications, job_qualifications and users_qualification I need to select all the staff that are qualified to carry out a particular job. It would be useful if there was a statement such as ALL IN so for example the query would be ``` SELECT user_id FROM users_qualification WHERE qualification_id ALL IN ( SELECT qualification_id FROM job_qualifications WHERE jobs_id = 1 ) ```

Original source