Barry brought an interesting point to me today. He needed to find if there existed any values for a particular set. For example this was his test dataset:


CREATE TABLE test (
id INT UNSIGNED NOT NULL,
foo INT UNSIGNED
);

INSERT INTO test (id, foo)
VALUES
(1, 1),
(1, 2),
(1, NULL),
(3, 4),
(3, 2),
(3, 3),
(4, 3),
(4, NULL),
(4, 5),
(5, 1),
(5, 1),
(5, NULL);


In this example he wanted to return all ids and if that id set contained any foos with a value of 1 or 2.

After bandying about for a bit he discovered that you can actually apply a limiter within an aggregate function.

So to find out which ids had a corresponding foos of 1 or 2 could be found from the following:

mysql> SELECT id, MAX(foo IN (1,2)) FROM test GROUP BY id;
+----+-------------------+
| id | MAX(foo IN (1,2)) |
+----+-------------------+
| 1 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 1 |
+----+-------------------+
4 rows in set (0.00 sec)


You could also apply a SUM function to determine how many results you actually had that matched your criteria.

January 13th, 2009 | Programming | 2 comments