MySQL Variables, GROUP_CONCAT, and using it later

Question

I am storing a hard list of

SELECT @items := GROUP_CONCAT(ID) FROM table_1 ... etc

@items is now a string of numbers: 55,77,99,2038,2844,etc

Later, I try to use it in a where clause as such:

SELECT * FROM table_2 WHERE table_1.ID IN (@items)

This does not work. It seems like it should. I know when I manually pull the data, put it in a variable, then output it it works:

list($x) = SELECT @items := GROUP_CONCAT(ID) FROM table_1 ... etc
$goodResults = SELECT * FROM table_2 WHERE table_1.ID IN ($x)

Any ideas? Thanks.

Answer 1

You may want to use the FIND_IN_SET() function:

SELECT * FROM table_1 WHERE FIND_IN_SET(id, @items) > 0;

Test case:

CREATE TABLE table_1 (id int, group_id int);
INSERT INTO table_1 VALUES (1, 1);
INSERT INTO table_1 VALUES (2, 1);
INSERT INTO table_1 VALUES (3, 1);
INSERT INTO table_1 VALUES (4, 1);
INSERT INTO table_1 VALUES (5, 1);

SELECT @items := GROUP_CONCAT(id) FROM table_1 GROUP BY group_id;

SELECT * FROM table_1 WHERE FIND_IN_SET(id, @items) > 0;
+------+----------+
| id   | group_id |
+------+----------+
|    1 |        1 |
|    2 |        1 |
|    3 |        1 |
|    4 |        1 |
|    5 |        1 |
+------+----------+
5 rows in set (0.02 sec)

SQL FIDDLE

Answer 2

"@items is now a string of numbers". The IN clause expects a set.

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in