SQL Select where column IN inclusive

Question

I have two tables Person and Skill.

Person:

    PersonID: AutoNumber
    PersonName: String

Skill:

    SkillID: AutoNumber
    SkillName: String

I have a join table:

PersonSkills

    PersonID: Number (FK to Person.PersonID)
    SkillID: Number  (FK to Skill.SkillID)

I have two rows in the Person table

PersonID   PersonName
1          Bob
2          John

And Four rows in the Skill table

SkillID    SkillName
1          English
2          French
3          Math
4          Science

And the Join table has

PersonID     SkillID
1            1             (Bob - English)
1            3             (Bob - Math)
1            4             (Bob - Science)
2            2             (John - French)
2            3             (John - Science)

I want to get a list of all people that have English, Math and Science as a skill (inclusive). A standard select

SELECT Person.PersonName, Skill.SkillName
FROM   Person left outer join PersonSkills on Person.PersonID = 
PersonSkills.PersonID left outer join on Skills on PersonSkills.SkillID = 
Skills.SkillID
where Skill.SkillName in ('English','Math','Science')

will retrieve both Bob and John.

How do I get the only results where all three skills are present?

Answer 1

Aggregate by person and then assert that all skills be present for each matching person:

SELECT p.PersonName
FROM Person p
INNER JOIN PersonSkills ps ON p.PersonID = ps.PersonID
INNER JOIN Skills s ON ps.SkillID = s.SkillID
WHERE s.SkillName IN ('English', 'Math', 'Science')
GROUP BY p.PersonName
HAVING COUNT(DISTINCT s.SkillName) = 3;