John Reilly

Problem: You want to return a set of employees that have all of the necessary skills to perform a particular task. A generalization of this would be something like, “return the objects in set A which contain all of the properties of a chosen item of set B”.

You have a table of Employees and a table of Tasks. Both make use of a common Skills table. The Employee table is linked to the Skills table by the table EmployeeHasSkill. The Task table is linked to the Skills table by TaskRequiresSkill.

My Solution: This is tricky because a Task can have any number of necessary Skills and an Employee must have ALL of these skills to be returned in the result set. The following query will return the employees that have all the necessary skills for a given task:

SELECT
	EmployeeId
FROM
	EmployeeHasSkill
WHERE
	SkillId IN (
		SELECT SkillId
		FROM TaskNeedsSkill
		WHERE TaskId = @TaskId
	)
GROUP BY
	EmployeeId
HAVING
	COUNT(TaskId) = (
		SELECT COUNT(SkillId)
		FROM TaskNeedsSkill
		WHERE TaskId = @TaskId
	)

This works by first returning all the rows in EmployeeHasSkill that match the skills needed for a task. Then it groups these results by employee. Finally, it returns only the employees that have all of the requisite skills (i.e. only the employees with a TaskId count that matches the SkillId count of the Task), ignoring any employees that only have some of the required skills.

There’s a good chance I’m over-complicating things and there exists some simple functionality to achieve this same result. If so, please post about it in the comments!