How to MySQL select from a single matching-table with multiple criteria (AND / OR doesn’t work)?

I have a very simple matching table with the following information (in real life the table contains different ids for other tables instead of fruit/colors, but the fruit example makes it easier to understand I hope):

[ID]  [FRUIT]     [POSSIBLECOLORS]
1     apple       green
2     apple       red
3     apple       yellow
4     banana      yellow
5     banana      brown
6     strawberry  red
7     cherry      red

What I want to do now is a select, that returns for example only fruits that match the POSSIBLECOLORS green AND yellow at the same time. In this example the result I want is “apple”, because it is the only fruit, that matches both colors.

If I do

SELECT * FROM table WHERE possiblecolors = green AND possiblecolors = yellow GROUP BY fruit

then my result is of course NULL, because no entry matches both in one row.

If I do

SELECT * FROM table WHERE possiblecolors = green OR possiblecolors = yellow GROUP BY fruit

then my result is of course “apple” and “banana”, as both have an entry that says yellow.

So how can I design a MySQL select that returns only “apple”?

Answer

If you want just the list of fruit, then use aggregation and having:

select fruit
from t
where possiblecolor in ('green', 'yellow')
group by fruit
having count(*) = 2;

Advertisement

Answer