Choose people who like apple and bananas, like

Question

How to choose people like apple and banana, as from the data below?

table: MyTable

persons | fruit ----------------------------- P1 Apple P1 Banana P1 Mango P2 Banana P2 Apple P3 Mango P3 Apple 

In this case, P1, P2 should be the result.

I tried using

 select * from MyTable where fruit in("Apple","Banana"); 

It is also the result of P3, because P3 also has an apple.

Thanks for any help.

Answer 1

 SELECT a.persons FROM MyTable a JOIN MyTable b on a.persons=b.persons WHERE a.fruit='Apple' and b.fruit='Banana' 

Answer 2

Try the following:

 SELECT persons FROM MyTable WHERE fruit IN ('Apple', 'Banana') GROUP BY persons HAVING COUNT(DISTINCT fruit) = 2; 

Answer 3

This will work:

 SELECT distinct `t1`.`persons` FROM MyTable AS `t1` INNER JOIN MyTable AS `t2` ON `t1`.`persons` = `t2`.`persons` WHERE `t1`.`fruit` = 'Banana' AND `t2`.`fruit` = 'Apple' 

Answer 4

 select * from MyTable where fruit in("Apple") and persons in(select persons from MyTable where fruit in("Banana"); 

Answer 5

Try the following:

 select persons from MyTable where fruit in("Apple","Banana");