MySQL: Select records where joined table matches ALL values

Question

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

is this you want ? SELECT DISTINCT(em.ID), em.Name ,skill.Name FROM employee em INNER JOIN emp_Skills es ON es.emp_ID = em.ID LEFT JOIN skill ON skill.id= es.skill_id WHERE es.skill_ID IN ('1', '2') — user12, Feb 07 '14 at 04:49

StuartLC · Accepted Answer · 2014-02-07T04:57:01.683

3

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.

edited Feb 07 '14 at 04:57

answered Feb 07 '14 at 04:47

StuartLC

104,537
17
209
285

Thanks Stuart, that did the trick. I have never used nested queries before. – dalatron Feb 17 '14 at 23:39
1

I love stack's suggested post feature...I had the exact same questions and this did the trick! Thanks! – Rocco The Taco Apr 04 '14 at 18:05

score 3 · Answer 2 · answered Feb 07 '14 at 05:00

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.

MySQL: Select records where joined table matches ALL values

2 Answers2

Linked