mysql - Selecting rows whose foreign rows ONLY match a single value -

say have 2 tables --people , pets-- each person may have more 1 pet:

people:

+-----------+-------+ | person_id | name  | +-----------+-------+ |         1 | bob   | |         2 | john  | |         3 | pete  | |         4 | waldo | +-----------+-------+ 

pets:

+--------+-----------+--------+ | pet_id | person_id | animal | +--------+-----------+--------+ |      1 |         1 | dog    | |      2 |         1 | dog    | |      3 |         1 | cat    | |      4 |         2 | cat    | |      5 |         3 | dog    | |      6 |         3 | tiger  | |      7 |         3 | tiger  | |      8 |         4 | tiger  | |      9 |         4 | tiger  | |     10 |         4 | tiger  | +--------+-----------+--------+ 

i'm trying select people have tigers pets. 1 fits criteria waldo, since pete has dog well... i'm having trouble writing query this.

the obvious case select people.person_id, people.name people join pets on people.person_id = pets.person_id pets.animal = "tiger", returns pete , waldo.

it helpful if there clause pets.animal = "tiger", far know doesn't exist.

how query written?

 select people.person_id, people.name     people     join pets on people.person_id = pets.person_id    pets.animal = "tiger"      , people.person_id not in (select person_id pets animal != 'tiger');