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.

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

That does it! Many thanks for your help!