mysql - MySQL:列出具有一对多关系但没有另一对多关系的行

不太清楚该如何问或定义，但无法弄清楚。
我有三张这样的桌子：

persons             person_id, first_name, last_name
hobbies             hobby_id, name
persons_hobbies     person_id, hobby_id

我要列两张单子。有A和B两个爱好的人，以及有A但没有B两个爱好的人。我怎样才能写出这两个问题？我不知道怎么才能加入。。。
假设爱好A的id=3，而爱好B的id=7。

最佳答案

这样的做法应该管用：

-- Persons that have both hobby A and B
select p.first_name,p.last_name
from persons p
inner join persons_hobbies ph1 on ph1.person_id = p.person_id and ph1.hobby_id = 3
inner join persons_hobbies ph2 on ph2.person_id = p.person_id and ph2.hobby_id = 7;

-- Persons that have hobby A but not B
select p.first_name,p.last_name
from persons p
inner join persons_hobbies ph1 on ph1.person_id = p.person_id and ph1.hobby_id = 3
left outer join persons_hobbies ph2 on ph2.person_id = p.person_id and ph2.hobby_id = 7
where ph2.person_id is null;