MySQL variables, GROUP_CONCAT and subsequent use

Question

I keep a hard list

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

@items is now a string of numbers: 55.77.99,2038.2844, etc.

Later I will try to use it in the 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 out the data, put it in a variable, and then output it:

 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 can 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 is awaiting set.

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

Answer 3

From the MySQL manual :

"User variables are connection specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits."

Since you marked "later", it is possible that the connection that created the variable was destroyed and the variable was lost.

Could you temporarily store the value of a variable in a table?