Geek asks and answers

MySQL where everything

I have five tables in my database, users, jobs, qualifications, job_qualifications and users_qualification

I need to select all the employees who can do a specific job. It would be useful if an operator such as ALL IN existed, 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
    )
+5
source share
2 answers

If you know the number of qualifications required for the assignment, 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
+6
source

Turning around the Scrum Meister answer, you can do something like this:

SELECT
    distinct 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(*) = (SELECT COUNT(*) FROM job_qualifications jq2 WHERE jq2.job_id = jq.job_id)
+2
source

More articles:


All Articles