How to check if value exists in each group (after group by)

Question

Assume I have a subscriptions table :

uid  | subscription_type  ------------------------   Alex | type1 Alex | type2 Alex | type3 Alex | type4 Ben  | type2 Ben  | type3 Ben  | type4 

And want to select only the users that have more than 2 subscriptions but never subscribed with type 1

The expected result is selecting "Ben" only.

I easy can found the users that have more than 2 subscribes using:

SELECT uid FROM subscribes GROUP BY uid HAVING COUNT(*) > 2 

But how to check if in a group some value never exists?

Thanks for the help!

Answer 1

Try this query:

SELECT uid  FROM subscribes  GROUP BY uid  HAVING COUNT(*) > 2    AND max( CASE "subscription_type"  WHEN 'type1' THEN 1 ELSE 0 END ) = 0