how to select data depending on multple row's field

Question

hi, i have a table like this:

+------------------------+
|Key| value | name       |   
+---+-------+------------+      
  1     A       john    
  2     B       Mary    
  3     C       Ben    
  4     A       Ben    
  5     B       john    
  6     C       Mary

how can i do a select which returns who has values of "A" and "B" (in this case, only "john")

I have this ugly one sql now..

SELECT * FROM table where value="A" or value="B" group by name having count(name) >1 ;

can someone tell me an more elegant way?

Answer 1

How about this:

select distinct t1.name from tab t1, tab t2 
where t1.name = t2.name and 
t1.value='A' and t2.value='B';

Answer 2

Your answer isn't that messy at all. You can't select a meaningful value for key though, and know what values you're looking for in key. So it could be a bit more neatly written as

SELECT name
 FROM table
 WHERE key IN ('A', 'B')
 GROUP BY name
 HAVING COUNT(*) = 2

Which can scale easily to any number of values or even x of y keys.