Javi Flores Javi Flores - 6 months ago 11
SQL Question

Query multiple rows and columns on a single table in postgres

I have the following table


|------------------------|
| owner | animal | color |
|------------------------|
| John | dog | black |
| Peter | dog | brown |
| John | cat | green |
| Lisa | dog | white |
| Peter | cat | black |
|------------------------|


I need to return which owner has a black dog AND a green cat, the result must be 'John'

I tried this with no luck


SELECT owner FROM pets
WHERE
(
EXISTS ( SELECT * FROM pets WHERE animal = 'dog' AND color = 'black' )
AND
EXISTS ( SELECT * FROM pets WHERE animal = 'cat' AND color = 'green' )
)

Answer

Select all those that have at leas one of those pets:

select owner
from pets
where (animal, color) in ( ('dog', 'black'), ('cat', 'green'))

This would also return owner that only have a black dog or a green cat, so we need to filter out those that do have two animals this can be done using a group by and a having clause

select owner
from pets
where (animal, color) in ( ('dog', 'black'), ('cat', 'green'))
group by owner
having count(*) = 2;

SQLFiddle example: http://sqlfiddle.com/#!15/4df52/1

Comments