mysql 多对多 过滤,使用多对多表MySQL进行过滤

I have the following tables (other tables ommitted for simplicity). 1 is for all of the people, 2 is for the sports that those people play. I am using php to allow a user to see a list of people. They can filter by a person's name, or by the sports they play. So, I want the ability to see all people that play for example baseball and football.

create table people (

id int,

name varchar(50)

);

create table people_to_sports (

personID int,

sportID int,

primary key(personID,sportID)

);

Basically my question is, how can I use people_to_sports to get a list of all people that play sport 1 and sport 2 for example?

I have a sqlfiddle here.

Thank you!

解决方案SELECT

personID

FROM

people_to_sports

WHERE

sportID IN (1, 2)

GROUP BY

personID

HAVING

COUNT(*) = 2