ANDed SQL filtering

Ever wanted to filter a SQL result set based on AND operations? It’s easy. Consider the foods table:

mysql> SELECT * FROM foods;
+---------+---------+
| food    | tag     |
+---------+---------+
| bagel   | yummy   | 
| bagel   | lunch   | 
| bagel   | healthy | 
| burrito | yummy   | 
| burrito | lunch   | 
| cereal | yummy   | 
+---------+---------+
5 rows IN SET (0.00 sec)

It’s trivial to write a SQL SELECT that returns food that is 'yummy' OR 'lunch':

  SELECT food FROM foods WHERE tag IN ('yummy', 'lunch')

But. Meh. You can’t figure out what to eat for lunch because this would match cereal, which is 'yummy' but isn’t 'lunch'.

So, for lunch, what’s really useful is seeing results that are 'lunch' AND also 'yummy'. The trick is to use GROUP BY and HAVING clauses:

Find yummy lunches…

mysql> SELECT food FROM foods WHERE tag IN ('yummy', 'lunch') GROUP BY food HAVING count(*) = 2;
+---------+
| food    |
+---------+
| bagel   | 
| burrito | 
+---------+
2 rows IN SET (0.00 sec)

But, if you’re like me, you want healthy yummy lunches…

mysql> SELECT food FROM foods WHERE tag IN ('yummy', 'lunch', 'healthy') GROUP BY food HAVING count(*) = 3;
+-------+
| food  |
+-------+
| bagel | 
+-------+
1 row IN SET (0.00 sec)

Decision made. Yay. Bagel for lunch. (NB: Bagels aren’t actually healthy.)

About this entry

You’re currently reading “ANDed SQL filtering,” an entry on e-huned.com

Published:
02.25.09 / 10am
Category:
personal