select persons who like apple and banana both

Question

How to select people like apple and banana both from the data below?

table: MyTable

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

i.e in this case, P1, P2 should be the result.

I tried with

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

This is also resulting P3 because P3 also have apple.

Thanks for any help.

Note that while doing group by and selecting another column does not gurantee which value it will return, in your case it will return 2 person p1 and p2 but may be since you are selecting * it will end up showing apple in fruit column. If you want both to get display use `group_concat` — Abhik Chakraborty, Dec 23 '14 at 08:45

score 4 · Accepted Answer · answered Dec 23 '14 at 08:35

4

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

answered Dec 23 '14 at 08:35

Matt Healy

18,033
4
56
56

Saharsh Shah · Answer 2 · 2014-12-23T08:47:42.497

1

Try this:

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

edited Dec 23 '14 at 08:47

answered Dec 23 '14 at 08:31

Saharsh Shah

28,687
8
48
83

Its resulting P1,Apple @Saharsh – Fahad Khan Dec 23 '14 at 08:36

score 1 · Answer 3 · answered Dec 23 '14 at 08:35

1

This would 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'

answered Dec 23 '14 at 08:35

Uriil

11,948
11
47
68

score 0 · Answer 4 · answered Dec 23 '14 at 08:34

0

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

answered Dec 23 '14 at 08:34

starko

1,150
11
26

score -1 · Answer 5 · edited Dec 23 '14 at 09:31

-1

Try this:

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

edited Dec 23 '14 at 09:31

DPenner1

10,037
5
31
46

answered Dec 23 '14 at 09:10

saish

1
1

select persons who like apple and banana both

5 Answers5