SQL “Contains All” Query | John Reilly
John Reilly

SQL “Contains All” Query

by admin on Dec.01, 2009, under SQL

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:

	SkillId IN (
		SELECT SkillId
		FROM TaskNeedsSkill
		WHERE TaskId = @TaskId
	COUNT(TaskId) = (
		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!

Comments are closed.

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!


All entries, chronologically...