MySQL: creating comma separated list and using in IN

Question

My function code is like following.

DECLARE ids VARCHAR(55);
SELECT  GROUP_CONCAT(id) INTO ids 
        FROM tableName WHERE ...;
SELECT CONCAT(GROUP_CONCAT(id), ids ) INTO ids FROM tableName .....;
SELECT column_name FROM tableName WHERE id IN (**ids**);

In this i am creating id list with two select statement and applying into IN list. Its giving me null result . if i CAST the ids in like CONCAT(GROUP_CONCAT(id), CAST(ids as char) ) then its giving me first row result.

score 0 · Answer 1 · answered Dec 30 '14 at 09:27

0

Try this:

DECLARE ids VARCHAR(55);
SELECT GROUP_CONCAT(id) INTO ids 
FROM (SELECT id 
      FROM tableName 
      WHERE...
      UNION 
      SELECT id 
      FROM tableName
      WHERE....
) AS A;

SELECT column_name FROM tableName WHERE FIND_IN_SET(id, ids);

answered Dec 30 '14 at 09:27

Saharsh Shah

28,687
8
48
83

This is right but it would be not work in temporary table case. – Ammar Hayder Khan Jan 01 '15 at 04:44

score 0 · Accepted Answer · answered Jan 01 '15 at 05:03

I have solved the problem by the following code

DECLARE ids VARCHAR(55);
SELECT GROUP_CONCAT(id SEPARATOR ',') INTO ids  FROM tableName WHERE ...;
SELECT CONCAT_WS(',', CAST(GROUP_CONCAT(id SEPARATOR ',')AS CHAR), CAST(ids AS CHAR)) INTO ids FROM tableName .....;
SELECT column_name FROM tableName WHERE FIND_IN_SET (id, ids );

MySQL: creating comma separated list and using in IN

2 Answers2