Query multiple rows and columns on a single table in postgres

Question

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

user330315 · Accepted Answer · 2016-05-18 22:35:58Z

1

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;

Collectives™ on Stack Overflow

Query multiple rows and columns on a single table in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related