MySQL select multiple values from column

Question

Here is the table

ID    WHO    FRUIT
1     Adam   Apple
2     Adam   Lemon
3     Eve    Apple
4     Adam   Grape
5     God    Papaya
6     Eve    Melon

How do I get all persons who have apple and lemon: in this case, so that I get the result Adam?

Furthermore, I want all persons who have apple and lemon or melon, so I would get Adam and Eve?

Answer 1

Use a self join on the table.

First one:

SELECT t1.who
FROM   table t1
JOIN   table t2
ON     t1.who = t2.who
WHERE
       t1.fruit = 'Apple'
AND    t2.fruit = 'Lemon'

Second one:

SELECT t1.who
FROM   table t1
JOIN   table t2
ON     t1.who = t2.who
WHERE
       t1.fruit = 'Apple'
AND    ( t2.fruit = 'Lemon' OR t2.fruit = 'Melon' )