CODEWITHSUNDEEP
sql
Ewgenij Sokolovski
Ewgenij Sokolovski

Reputation: 967

Query for a skill database

I designed following skill database with three tables:

  1. employees with columns name, personnelnumber
  2. skills with columns skillname, skillid
  3. skillmapping with columns skillid, personnelnumber, skilllevel

1) and 2) just assign numbers to persons and skills. 3) assigns skill levels to employees.

For example, row 1 3 6 in table 3) means person with personal number 3 has knowledge of skill number 1 and the knowledge level is 6.

What I want is to retrieve persons who at the same time are skilled in

  1. skill 2 with knowledge level 3
  2. skill 4 with knowledge level 5 and
  3. skill 8 with knowledge level 2.

What would be the best way to do that? I thought about first selecting persons who meet condition 1), then select from them persons who meet condition 2) and then select from the result people meeting condition 3). But for that approach I have to create temporary tables and go beyond SQL using some procedural programming language. Isn't there a better approach?

Upvotes: 0

Views: 1731

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

Should be a fairly straight forward inner join;

SELECT e.*
FROM employees e
JOIN skillmapping s1 ON s1.personnelnumber = e.personnelnumber 
                    AND s1.skillid   = 2
                    AND s1.skillevel = 3
JOIN skillmapping s2 ON s2.personnelnumber = e.personnelnumber 
                    AND s2.skillid   = 4
                    AND s2.skillevel = 5
JOIN skillmapping s3 ON s3.personnelnumber = e.personnelnumber 
                    AND s3.skillid   = 8
                    AND s3.skillevel = 2

Upvotes: 2

AlexT82
AlexT82

Reputation: 1124

QUERY (i use EXISTS functionality to check if that combination exists...):

SELECT * FROM Employees WHERE 
EXISTS (SELECT * FROM skillmapping t2 WHERE t2.PersonnelNumber = Employees.PersonnelNumber AND SkillId = 2 AND t2.SkillLevel >= 3) --NOTE: lvl 3 or more!
AND EXISTS (SELECT * FROM skillmapping t2 WHERE t2.PersonnelNumber = Employees.PersonnelNumber AND SkillId 4 AND t2.SkillLevel >= 5) -- NOTE: lvl 5 or more 
AND EXISTS (SELECT * FROM skillmapping t2 WHERE t2.PersonnelNumber = Employees.PersonnelNumber AND SkillId = 8 AND t2.SkillLevel >= 2) -- NOTE: lvl 2 or more

Upvotes: 1

Mureinik
Mureinik

Reputation: 311843

I'd use a subquery to count the number of skill matches a person has, and make sure it comes up as three:

SELECT name
FROM   person
WHERE  personnelnumber IN (SELECT   personnelnumber
                           FROM     skillmapping
                           WHERE    (skillid = 2 AND skilllevel = 3) OR
                                    (skillid = 4 AND skilllevel = 5) OR
                                    (skillid = 8 AND skilllevel = 2) 
                           GROUP BY personnelnumber
                           HAVING   COUNT(*) = 3)

Upvotes: 1

Related Questions