Shorthand for AND when SELECTING with multiple WHERE conditions on same column

Question

I have the table PETS with columns Index, Owner and Animal with following rows:

Index, Owner, Animal
--------------------
1, Anne, cat
2, Anne, dog
3, Anne, rabbit
4, Bill, dog
5, Bill, dog
6, Bill, rabbit
7, John, cat
8, John, dog
9, Johh, parrot
10, Susy, rabbit
11, Susy, parrot
12, Susy, parrot

I need two outputs: Get all Owners which own

a cat OR a dog
a cat AND a dog

The first output I get with IN as shorthand operator for OR:

SELECT DISTINCT Owner FROM PETS WHERE Animal IN ('cat','dog')

Is there an equivalent shorthand operator for AND? The solution in this thread with HAVING COUNT does not quite work as it returns 'Bill' as there is 'dog' twice, however there is no 'cat'.

You could pivot this data on owner column and then write a sane `WHERE` statement, perhaps? — Tanveer Badar, Dec 11 '20 at 12:39
2 is just a special case of 1 unless there is more to your logic than what you suggest. — SMor, Dec 11 '20 at 13:12
Did you scroll down to the second answer in the question you linked to that pointed out it should be using `COUNT(DISTINCT Animal)` rather than `COUNT(*)`? — Damien_The_Unbeliever, Dec 11 '20 at 13:15
Damien_The_Unbeliever - please add your comment as answer so I can mark it. — stonebe, Dec 11 '20 at 15:06

score 0 · Answer 1 · answered Dec 11 '20 at 12:34

There isn't a shorthand, no, but personally I would use a HAVING with conditional aggregation for the owning both logic:

SELECT Owner
FROM dbo.Pets
GROUP BY Owner
HAVING COUNT(CASE Animal WHEN 'dog' THEN 1 END) > 0
   AND COUNT(CASE Animal WHEN 'cat' THEN 1 END) > 0;

And, obviously, you can easily change the above for your original requirement by changing the AND to an OR.

score 0 · Answer 2 · answered Dec 11 '20 at 13:16

The solution with HAVING COUNT does not quite work as it returns 'Bill' as there is 'dog' twice, however there is no 'cat'.

IN ('cat','dog') selects all owners which have a cat or a dog (consequently, owners which have a cat AND a dog). From the above cat_and/or_dog_owners, the count must be on DISTINCT animals: if a cat_and/or_dog_owner has 2 distinct animals then the owner has both a cat and a dog:

declare @t table (Id smallint, Owner varchar(10), Animal varchar(10))
insert into @t
values
--------------------
(1, 'Anne', 'cat'),
(2, 'Anne', 'dog'),
(3, 'Anne', 'rabbit'),
(4, 'Bill', 'dog'),
(5, 'Bill', 'dog'),
(6, 'Bill', 'rabbit'),
(7, 'John', 'cat'),
(8, 'John', 'dog'),
(9, 'Johh', 'parrot'),
(10, 'Susy', 'rabbit'),
(11, 'Susy', 'parrot'),
(12, 'Susy', 'parrot'),
(13, 'Susy', 'cat');--n

select owner , concat(min(animal), case when count(distinct animal) = 2 then ' & ' end, nullif(max(animal), min(animal))) as cat_dog_pet
from @t
WHERE Animal IN ('cat','dog')
group by owner;

Shorthand for AND when SELECTING with multiple WHERE conditions on same column

2 Answers2