MySQL: Select records where joined table matches ALL values

join, mysql, sql

Solution

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.

Problem

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` varchar(100) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; INSERT INTO `Employee` (`ID`, `Name`, `Region_ID`) VALUES (1, 'Fred Flintstone'), (2, 'Barney Rubble'); CREATE TABLE IF NOT EXISTS `Skill` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(100) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; INSERT INTO `Skill` (`ID`, `Name`) VALUES (1, 'PHP'), (2, 'JQuery'); CREATE TABLE IF NOT EXISTS `Emp_Skills` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `Emp_ID` bigint(20) unsigned NOT NULL DEFAULT '0', `Skill_ID` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; INSERT INTO `Emp_Skills` (`ID`, `Emp_ID`, `Skill_ID`) VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1); ``` Here is the query I have so far: ``` SELECT DISTINCT(em.ID), em.Name FROM Employee em INNER JOIN Emp_Skills es ON es.Emp_ID = em.ID WHERE es.Skill_ID IN ('1', '2') ``` This returns both employees, however, I need to find the employee that has both skills (ID 1 and 2). Any ideas? Thanks

Original source