SQL Group By equivalent
- by MikeB
Pretend I have a cupcake_rating table:
id | cupcake | delicious_rating
--------------------------------------------
1 | Strawberry | Super Delicious
2 | Strawberry | Mouth Heaven
3 | Blueberry | Godly
4 | Blueberry | Super Delicious
I want to find all the cupcakes that have a 'Super Delicious' AND 'Mouth Heaven' rating. I feel like this is easily achievable using a group by clause and maybe a having.
I was thinking:
select distinct(cupcake)
from cupcake_rating
group by cupcake
having delicious_rating in ('Super Delicious', 'Mouth Heaven')
I know I can't have two separate AND statements. I was able to achieve my goal using:
select distinct(cupcake)
from cupcake_rating
where cupcake in ( select cupcake
from cupcake_rating
where delicious_rating = 'Super Delicious' )
and cupcake in ( select cupcake
from cupcake_rating
where delicious_rating = 'Mouth Heaven' )
This will not be satisfactory because once I add a third type of rating I am looking for, the query will take hours (there are a lot of cupcake ratings).
