11
col1 col2
A bear
A dog
A cat
B bear
B dog
B cat
C dog
C cat
D bear
D dog
D cat
E bear
E dog
E cat
F dog
F cat
if I wanted to select all of the col1 values that have at least one row where col2 = 'bear', I can do:
SELECT col1
FROM mytable
WHERE col1 IN ('A','B','C')
GROUP BY col1
HAVING col2 = 'bear'
which would return A and B
but I want to select only the values from col1 where there isn't a row where col2 = 'bear'
I'm thinking NOT HAVING, but that doesn't appear to work.
any ideas? thanks!