MySQL selects multiple values ​​from a column

Question

Here is a table

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

How to get all the people who have an apple and a lemon: in this case, to get the result of Adam?

Also, I need all the people who have an apple, lemon or melon, so would I get Adam and Eve?

Answer 1

Use the self-join in the table.

The first:

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

Second:

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' )