mysql匹配表_MySQL：选择连接表匹配所有值的记录

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

解决方案

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;

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

Thanks for the test data.