SQL Group By equivalent

Posted by MikeB on Stack Overflow See other posts from Stack Overflow or by MikeB
Published on 2013-06-26T20:20:53Z Indexed on 2013/06/29 16:21 UTC
Read the original article Hit count: 271

Filed under:
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle