filtering for multiple values on one column. All values must exist, else - return zero

Posted by Andrew on Stack Overflow See other posts from Stack Overflow or by Andrew
Published on 2011-01-02T20:38:29Z Indexed on 2011/01/02 20:54 UTC
Read the original article Hit count: 149

Filed under:
|
|

Hello All,

I would like to filter one column in a table for couple values and show results only if all those values are there. If one or more is missing, then return zero results.

example table

+----+--------+----------+
| id | Fruit  | Color    | 
+----+--------+----------+
| 1  | apple  | red      | 
| 2  | mango  | yellow   | 
| 3  | banana | yellow   | 
+----+--------+----------+

example "wrong" code: (this must return 3 rows)

select Fruit FROM table WHERE Color = red AND Color = yellow

but

select Fruit FROM table WHERE Color = red AND Color = green

must return 0 rows.

(If i use select Fruit FROM table WHERE Color = red OR Color = green i get 1 row which is not what i need)

I am using PHP with form where user checks different checkboxes that represent different values of the same column. So when he selects multiple checkboxes, all those values should be in the result set, otherwise no result should be given.

Thank you,

Andrew

© Stack Overflow or respective owner

Related posts about php

Related posts about sql