CODEWITHSUNDEEP
mysqlsqlpostgresql
aralar
aralar

Reputation: 3120

Selecting rows whose foreign rows ONLY match a single value

Say I have two tables --people and pets-- where each person may have more than one 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 to select the people who ONLY have tigers as pets. Obviously the only one that fits this criteria is Waldo, since Pete has a dog as well... but I'm having some trouble writing the query for this.

The most obvious case is select people.person_id, people.name from people join pets on people.person_id = pets.person_id where pets.animal = "tiger", but this returns Pete and Waldo.

It would be helpful if there was a clause like pets.animal ONLY = "tiger", but as far as I know this doesn't exist.

How could the query be written?

Upvotes: 0

Views: 60

Answers (5)

David Aldridge
David Aldridge

Reputation: 52336

If every person was guaranteed to have at least one pet, then the query could be as simple as:

select name
  from people
 where not exists (select 1
                     from pets
                    where pets.person_id = people.id and
                          pets.animal   != 'tiger')

Or: return the people for whom there is no record that is not a tiger.

NOT EXISTS is executed as a very efficient anti-join, in which each row from people would be rejected as soon as a single non-tiger pet was found.

Upvotes: 1

Caullyn
Caullyn

Reputation: 166

 select people.person_id, people.name 
   from people 
   join pets on people.person_id = pets.person_id 
  where pets.animal = "tiger" 
    AND people.person_id NOT IN (select person_id from pets where animal != 'tiger');

Upvotes: 1

wildplasser
wildplasser

Reputation: 44220

SELECT *
FROM people pp
WHERE EXISTS (SELECT * FROM pets pt
   WHERE pt.person_id = pp.person_id
   AND pt.animal = 'tiger'
   )
AND NOT EXISTS (SELECT * FROM pets pt
   WHERE pt.person_id = pp.person_id
   AND pt.animal <> 'tiger'
   );

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select distinct person_id
from pets 
where animal = "tiger"
intersect
select distinct person_id
from pets 
where animal = "tiger" 
and person_id not in 
(select person_id from pets where animal <> "tiger")

You can use intersect to select a person who only has tiger as his pet.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use group by and having:

select p.person_id
from pets p
group by p.person_id
having max(animal) = 'tiger' and min(animal) = 'tiger';

Upvotes: 1

Related Questions