MySQL’s GROUP_CONCAT command is a phantastic thing, it can simplify big efforts to a small piece of work that would not even be worth mentioning – if the way that the problem is being solved wouldn’t be so nice ;).
The reason why I’m writing this is because I have to work on such a task with PostgreSQL, and I already know that it will be quite a tough piece of work, which could be solved with MySQL within seconds. So to everybody, who doesn’t still know the power of the GROUP_CONCAT aggrigate function, here’s a little example, which I think is self-explaining:
mysql> CREATE TABLE group_concat_test (
-> id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
-> _key INT UNSIGNED NOT NULL,
-> _value CHAR(1) NOT NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO group_concat_test
-> (_key, _value) VALUES
-> (1, 'b'),
-> (1, 'c'),
-> (2, 'a'),
-> (2, 'd'),
-> (2, 'c'),
-> (3, 'b'),
-> (3, 'e');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT _key,
-> COUNT(*) as cnt,
-> GROUP_CONCAT(_value ORDER BY _value SEPARATOR ', ')
-> as value_list
-> FROM group_concat_test
-> GROUP BY _key
-> ORDER BY _key;
+------+-----+------------+
| _key | cnt | value_list |
+------+-----+------------+
| 1 | 2 | b, c |
| 2 | 3 | a, c, d |
| 3 | 2 | b, e |
+------+-----+------------+
3 rows in set (0.00 sec)
mysql>
The GROUP_CONCAT function is available since MySQL 4.1, so for everyone still running an older version, this would be one more (of many) reason to update.
if I were to search for “b” and “c” in your value list column, all 3 rows will be returned. How can I filter it to make it return the row with “b” and “c” only. i.e. row 1.
Thanks a lot
This self join should do the trick: