need help with a complex nested query 1

[dannysm]

Need help writing a complex mySQL query. Here's a description of the problem reduced to its simplest terms:
Given 3 primary tables (skill, task, user) each with 2 fields (an id and a name), and 2 linking tables (task_skill, user_skill), each with two foreign keys into the corresponding tables. I want to get a list of the tasks that each user can perform.
E.g., Given the following:
Alice can do skills 1, 2, 3. Bob can do 1, 3, 4.
Task_a requires skills 1, 2, 3. Task_b requires sills 1, 4.
Produce output that Alice can do Task_a, and Bob can do Task_b.

There are hundreds of skills, and task can require up to a few dozen skills, so having a column as a set type is not a possibility.

Doing this in memory with 3 nested loops would be simple, but is there a way to do this in SQL using nested queries? I'm guessing somewhere along the line set subtraction would be needed, but I can't see how to get things grouping correctly.

Many thanks for any pointers. Attached is a db dump that sets up the necessary tables.

 

[r937]

SELECT user.user_id
     , user.user_name
     , task.task_id
     , task.task_name
     , ts.task_skills
  FROM user
INNER
  JOIN user_skill
    ON user_skill.user_id = user.user_id
INNER
  JOIN skill
    ON skill.skill_id = user_skill.skill_id
INNER
  JOIN task_skill
    ON task_skill.skill_id = skill.skill_id
INNER
  JOIN task
    ON task.task_id = task_skill.task_id
INNER
  JOIN ( SELECT task_id
              , COUNT(*) AS task_skills
           FROM task_skill
         GROUP
             BY task_id ) AS ts
    ON ts.task_id = task.task_id
GROUP
    BY user.user_id
     , user.user_name
     , task.task_id
     , task.task_name
     , ts.task_skills
HAVING COUNT(*) = ts.task_skills

the data that you provided has task 1 requiring skills 1,2,3, but task 2 requiring skills 1 and 3, not 1 and 4 as you posted above

thus, alice can do both tasks 1 and 2, not just task 1, and bob can still do task 2



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dannysm]

That does it! Many thanks for your help!