CODEWITHSUNDEEP
sqlpostgresql
Javi Flores
Javi Flores

Reputation: 13

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' )
)

Upvotes: 1

Views: 523

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions