How would I find records with a specific set of related records?

[Steven Kogan]

I'm working on an Employee database and would like to find employees who have experience in several categories. There is an employee table and a related employee experience table. For simplicity let's say the employee table has just the field EmployeeID and the EmployeeExperience table has fields EmployeeID and ExperienceCategoryID. I want to find employees with ExperienceCategoryID values 2, 3, 8, and 10. Finding an employee with any one of these categories is easy, but I want to find employees with all of the selected categories. How would I do that?

[Rabbit]

Assuming that the combination of EmployeeID and ExperienceCategoryID is unique in the employee experience table, filter for those categories you're looking for, do a count grouping by the employee, return only those with a count of 4.

[Steven Kogan]

In my case the experience is also related to projects and date ranges so they can have the same experience listed several times. I could group by EmployeeID and ExperienceCategoryID, and then create another query doing what you described. Thanks!

[jforbes]

You could also try something like this:

SELECT Emp.EmployeeID
FROM Employee AS Emp
WHERE Emp.EmployeeID IN (SELECT EmpEx.EmployeeID FROM EmployeeExperience AS EmpEx WHERE EmpEx.ExperienceCategoryID=[Cat1] OR [Cat1] IS NULL)
AND Emp.EmployeeID IN (SELECT EmpEx.EmployeeID FROM EmployeeExperience AS EmpEx WHERE EmpEx.ExperienceCategoryID=[Cat2] OR [Cat2] IS NULL)
AND Emp.EmployeeID IN (SELECT EmpEx.EmployeeID FROM EmployeeExperience AS EmpEx WHERE EmpEx.ExperienceCategoryID=[Cat3] OR [Cat3] IS NULL)
AND Emp.EmployeeID IN (SELECT EmpEx.EmployeeID FROM EmployeeExperience AS EmpEx WHERE EmpEx.ExperienceCategoryID=[Cat4] OR [Cat4] IS NULL)

The ... OR [Cat1] IS NULL... statements allow you to pass null values if you have fewer than 4 Categories that you are trying to match.

[NeoPa]

Try this idea :

SELECT   [EmployeeID]
FROM     [EmployeeExperience]
WHERE    ([ExperienceCategoryID] In(2,3,8,10))
GROUP BY [EmployeeID]
Having   (Count(*)=4)

It's basically what Rabbit suggested in the first place :-D