Use GROUP_CONCAT result in IN Clause Mysql

Question

I have a Query which returns comma separated integers like :

select GROUP_CONCAT(ids) from table2

now I want to use that result in another query like :

select * from table1 where column in (select GROUP_CONCAT(ids) from table2)

in this case it will consider only first value in IN clause.

score 9 · Answer 1 · answered Nov 15 '13 at 12:02

9

I agree with @Alma that this can't be done with IN, you might be able to do it with FIND_IN_SET, but if you can do it with IN it's probably a better approach :

SELECT *
FROM table1
WHERE find_in_set(ids, (
      SELECT GROUP_CONCAT(ids)
      FROM table2
      )) != 0;

sqlfiddle demo

answered Nov 15 '13 at 12:02

Filipe Silva

21,189
5
53
68

its not a good solution when you can directly use `IN()` , `GROUP_CONCAT` is not advisable because it has a character limit you need to increase that one if you used in your answer you need to brief also,when it can be done in simple way then why make set first then apply `find_in_set` it makes sense ? – M Khalid Junaid Nov 15 '13 at 12:07
2

i'm not saying it is a good approach. i'm advising to use IN instead, but i think this is what he was asking for. – Filipe Silva Nov 15 '13 at 12:10
1

is there any other way, to use in in where clause for the group_concat() values other then find_in_set – pitu Nov 20 '14 at 07:32

score 2 · Answer 2 · answered Nov 15 '13 at 11:53

2

Any special reason not using a join and using a sub query

select * from table1 t1
JOIN table2 t2 on (t2.column = t1.ids)

answered Nov 15 '13 at 11:53

M Khalid Junaid

63,861
10
90
118

why should this be done? – Martin Apr 13 '22 at 16:52

Use GROUP_CONCAT result in IN Clause Mysql

2 Answers2