IN query is not working group_concat in mysql subquery

Question

IN query is not working in sub_query of mysql
Please take look-:

SELECT (SELECT GROUP_CONCAT( text ) 
        FROM course_intersted_in_list 
        WHERE id IN ( urd.interested_course )) as interested_course_text,
       urd.* 
from user_registration_data as urd

Here urd.interested_course have values like 1,2,3,4 for some users

user_registration_data table is like

id             interested_course
 1             1,2,3,4,5
 2             1,4,5

course_intersted_in_list table is like

id             interested_course
 1             mbbs
 2             dental
 3             basic

are these `urd.interested_course` values stored as comma separated values ? — M Khalid Junaid, Sep 18 '17 at 14:37
Check this link...https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — SQL.RK, Sep 18 '17 at 14:40

score 2 · Accepted Answer · answered Sep 18 '17 at 14:41

2

Since the interested_course has comma separated ids, you can use find_in_set to search a value in csv string:

SELECT (SELECT GROUP_CONCAT( text ) 
        FROM course_intersted_in_list 
        WHERE find_in_set(id, urd.interested_course)
        ) as interested_course_text,
       urd.* 
from user_registration_data urd;

Also, I recommend staying away from CSV data in RDBMS and normalize the data. that way you'll get a system that scales.

answered Sep 18 '17 at 14:41

Gurwinder Singh

38,557
6
51
76

Its working I forget about find in set , its working now – Vipul sharma Sep 18 '17 at 14:44
@vipulsharma if its working read here what to do with the working answers https://stackoverflow.com/help/someone-answers – M Khalid Junaid Sep 18 '17 at 14:57

score 1 · Answer 2 · answered Sep 18 '17 at 14:42

You can use MySQL FIND_IN_SET instead:

SELECT (SELECT GROUP_CONCAT( text ) 
        FROM course_intersted_in_list 
        WHERE find_in_set(id, urd.interested_course)) as interested_course_text,
       urd.* 
from user_registration_data as urd

IN() doesn't work on table columns, only against a list inside ()

IN query is not working group_concat in mysql subquery

2 Answers2