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?

martin clayton · Accepted Answer · 2009-12-16 08:21:40Z

6

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

edited Dec 16, 2009 at 8:21

answered Dec 16, 2009 at 8:07

martin clayton

78.6k33 gold badges220 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Leffler Over a year ago

The t1.fruit != t2.fruit condition is rather implied by the next two conditions, in both queries. No harm done - but not much good either.

Collectives™ on Stack Overflow

MySQL select multiple values from column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related