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 will return all names that have at least country and jazz as favorite music:

select name from table1 where
  favorite_music in ('country', 'jazz')
group by name having count(name) = 2

This will return all names that have exactly (not more) country and jazz as favorite music:

select name from table1 t1 where
not exists (
  select * from table1 t2 where
    t1.name = t2.name and
    t2.favorite_music not in ('country', 'jazz')
)
group by name having count(name) = 2

The double negative can be tricky. You can read it like this: select all people who don't like music that isn't country or jazz. That means that they only like music that's country or jazz. The group by and having count = 2 just takes care that what they like is country and jazz.

Answer 2

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

Answer 3

SELECT t1.name  
FROM table1 as t1  
INNER JOIN table1 AS t2 ON t1.name = t2.name 
WHERE t1.favorite_music = 'jazz' AND t2.favorite_music = 'country'  

Answer 4

Try this-->

select x.name from table1 x, table1 y
where
x.name = y.name and
x.favorite_music = 'country' and 
y.favorite_music = 'jazz'