根据另一列的值过滤一列上的多个值
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
之前我曾询问过滤一个列的多个值，其中所有列都必须出现在列中，否则没有结果 应给予。 我收到了很好的答案，但这次我的要求有所改变，我需要一些不同的东西。 p>
所以我需要为一对值过滤一列，并且只有当所有这些值都在那里时显示结果并且所有这些值都与另一列中的一个值相关。 p>
示例表： p>
+ ---- + -------- + ---------- +
| id | 水果| 颜色|
+ ---- + -------- + ---------- +
| 1 | 苹果| 绿色|
| 2 | 苹果| 黄色|
| 3 | 香蕉| 绿色|
| 4 | 香蕉| 黄色|
| 5 | 芒果| 绿色|
+ ---- + -------- + ---------- + p>
例如 如果提交了“绿色和黄色”值，则只有具有这两种颜色的水果应该在结果集中，在本例中为“apple”和“banana”。 所有其他行都应该被解雇。 p>
我正在使用带有php的MySQL。 p>
示例“错误的代码”： p>
选择Fruit FROM表WHERE颜色=绿色和颜色=黄色
code> pre>
必须返回前4行但不返回5。 p>
谢谢你
Andrew p>
div>
Assuming you need each fruit name only once:
SELECT Fruit FROM table WHERE Color IN ('green', 'yellow')
GROUP BY Fruit HAVING COUNT(*) >= 2
Well, you'll want to join the table on itself.
SELECT a.Fruit
FROM table AS a
JOIN table AS b ON a.Fruit = b.Fruit
WHERE a.Color = 'green'
AND b.Color = 'yellow'
But, instead, I'd suggest properly normalizing your data, which would make this query much easier (and much more efficient)...
So, in that case, you need all (fruit,color) pairs that appear exactly twice. You can use a count to filter that out, like so:
select fruit
from(
select fruit,count(1) as freq
from table
where color in (green,yellow)
group by 1
)freq_table
where freq=2;
This, of course, assumes that no (fruit,color) pair appears more than once. If so, then you might want a third level of subquerying where you select fruit,color from table group by 1,2.