Mysql join match multiple rows from each table

Question

Here is a simplified version of the pertinent part of my DB:

Person:
    id

PersonSkills:
    person_id
    skillname
    ability

Position:
    id

PositionSkills:
    position_id
    skillname
    ability

A person can have any number of PersonSkills, let's say in this instance, our user has 6. A Position can require any number of PositionSkills, let's say in this instance the position requires 3. I need a query that will determine whether ALL of the PositionSkills associated with this Position are present in the PersonSkills associated with this person. (I also need to ensure that the PersonSkill's ability is greater than the PositionSkill's ability, but I think that will be simple once I figure out the part that is giving me trouble here.)

Thanks in advance, Jason

EDIT

Here is more detail on what I'm looking for:

PersonSkills
+-------------+---------+---------+
| person_id   | skill   | ability |
+-------------+---------+---------+
|     1       |  A      | 5       |
|     1       |  B      | 4       |
|     1       |  C      | 5       |
|     1       |  D      | 4       |
|     1       |  E      | 5       |
+-------------+---------+---------+

PositionSkills
+-------------+---------+---------+
| position_id | skill   | ability |
+-------------+---------+---------+
|     5       |  A      | 3       |
|     5       |  B      | 3       |
|     5       |  C      | 3       |
|     6       |  A      | 3       |
|     6       |  B      | 3       |
|     6       |  Z      | 3       |
+-------------+---------+---------+

From the fact that I'm user 1, I want a query to tell me that I am qualified for Position 5, because I have skills A, B, and C, that it requires, but that I am not qualified for Position 6, because I lack skill Z

Thanks again, Jason

Zane Bien · Accepted Answer · 2012-07-06 02:33:08Z

3

Try this solution:

SELECT
    a.id,
    (COUNT(c.position_id) = (SELECT COUNT(*) FROM positionskills WHERE position_id = <position_id here>)) AS isQualified
FROM
    person a
LEFT JOIN
    personskills b ON a.id = b.person_id
LEFT JOIN
    positionskills c ON 
        b.skillname = c.skillname AND 
        b.ability >= c.ability AND
        c.position_id = <position_id here>
GROUP BY
    a.id
WHERE
    a.id = <person_id here>

If the person is qualified, isQualified will be 1 else it will be 0

EDIT: As per clarification in the question, to get all the positions for which the person is qualified for, use this solution:

SELECT
    a.position_id
FROM
    (
        SELECT bb.position_id, COUNT(*) AS skillshave
        FROM personskills aa
        INNER JOIN positionskills bb ON aa.skillname = bb.skillname AND aa.ability >= bb.ability
        WHERE aa.person_id = <person_id here>
        GROUP BY bb.position_id
    ) a
INNER JOIN
    (
        SELECT position_id, COUNT(*) AS skillsrequired
        FROM positionskills
        GROUP BY position_id
    ) b ON a.position_id = b.position_id AND a.skillshave = b.skillsrequired

edited Jul 6, 2012 at 2:33

answered Jul 5, 2012 at 2:38

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jsteele Over a year ago

Thanks for the response Zane. I don't actually know what the particular position_id is at the time of the query. Actually, the point of the query is to find out which positions the user is qualified for. I should have been more specific in the original post. Do you have any thoughts on how I might accomplish that?

Collectives™ on Stack Overflow

Mysql join match multiple rows from each table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related