A simple sql query question

Question

suppose I have a table like this:

table1:

name     favorite_music
 a         country
 b         rock
 a         jazz
 b         jazz

How can I do this query: find the names whose favorite_music style has both "country" and "jazz". For the example above, it should be only "a".

Answer 1

This should get them:

select name
from table1
where favorite_music = 'country'
intersect
select name
from table1
where favorite_music = 'jazz'

EDIT: The question is not very clear. The query above will return every name thas has both jazz and country as favorite music styles (in your example table, name='a')

EDIT 2: Just for fun, one example that should do it with one single scan, using a subquery:

select name from (
    select 
    name, 
    count(case when favorite_music = 'country' then 1 end) as likes_country,
    count(case when favorite_music = 'jazz' then 1 end) as likes_jazz,
    from table1
    where favorite_music in ('country', 'jazz')
    group by name
) where likes_country > 0 and likes_jazz > 0