MySQL: Select Records Where Joined Table Matches ALL Values

I'm trying to find all employees with multiple skills. Here are the tables: CREATE TABLE IF NOT EXISTS `Employee` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `Name` var

Solution 1:

This will do it:

SELECT EmpId, Name
FROM
(
   SELECT em.ID as EmpId, em.Name, es.ID as SkillID 
   FROM Employee em 
   INNER JOIN Emp_Skills es ON es.Emp_ID = em.ID
   WHERE es.Skill_ID IN ('1', '2')
 ) X
GROUP BY EmpID, Name
HAVING COUNT(DISTINCT SkillID) = 2;

Fiddle here:

The distinct is just in case the same employee has the skill listed twice.

Thanks for the test data.

Solution 2:

You can do this with aggregation and a having clause:

SELECT em.ID, em.Name 
FROM Employee em INNER JOIN
     Emp_Skills es
     ON es.Emp_ID = em.ID
GROUP BY em.id, em.name
HAVING sum(es.Skill_id = '1') > 0 and
       sum(es.Skill_id = '2') > 0;

Each condition in the having clause counts the number of rows for each employee that have a particular skill. The filter guarantees that both skills are present.