Previously I asked about filtering one column for multiple values where all of them must be present in the column, otherwise no results should be given. I recieved good answer, but this time my request changed somewhat and i need something different.
So I need to filter one column for couple values and show results only if all those values are there AND all those values are related to one value in another column.
Example table:
+----+--------+----------+
| id | Fruit | Color |
+----+--------+----------+
| 1 | apple | green |
| 2 | apple | yellow |
| 3 | banana | green |
| 4 | banana | yellow |
| 5 | mango | green |
+----+--------+----------+
E.g. if values 'green and yellow' are submitted, only fruits that have both of these colors should be in the result set, in this case 'apple' and 'banana'. All other rows should be dismissed.
I am using MySQL with php.
Example "wrong code":
select Fruit FROM table WHERE Color = green AND Color = yellow
Must return first 4 rows but not 5.
Thank you
Andrew