Find in set to get the separate values with comma-CodePudding

Before I have asked the same problem (Join table with comma issue (MySQL)) about join table with comma in the column.

I have two tables, table structure like below:

First Table name: student

id   | name   | course_id
——————————————————————————
1      David     1,3 
2      Peter     2,4
3      Shawn     2,6

Second Table name: subject

id    |    subject
———————————————————
1          English
2          Maths
3          Science
4          Geographic
5          Accounting
6          Art & Design

I have tried this find_in_set method (Search with comma-separated value mysql), but it cannot get the actual result. I want the actual result is like below:

id   | name | subject_name
——————————————————————————
1      David   English,Science
2      Peter   Maths,Geographic
3      Shawn   Maths,Art & Design

I am using below code:

SELECT student.id as id,student.name as name,student.subject as subject_name
FROM student
INNER JOIN subject
ON FIND_IN_SET(subject.id, student.course_id) > 0

But the result is shown me like below:

id   | name | subject_name
——————————————————————————
1      David   English
2      David   Science
3      Peter   Maths
4      Peter   Geographic
5      Shawn   Maths
6      Shawn   Art & Design

Hope someone guide me on how to solve this problem. Thanks.

CodePudding user response：

Like this

SELECT student.id as id, student.name as name, GROUP_CONCAT(subject.subject) as subject_name
FROM student
INNER JOIN subject
ON FIND_IN_SET(subject.id, student.course_id) > 0
GROUP BY student.id, student.name

Usually we don't concat everything in SQL query, but you can do

SELECT CONCAT_WS(' ', student.id, student.name, GROUP_CONCAT(subject.subject)) as concated_value
FROM student
INNER JOIN subject
ON FIND_IN_SET(subject.id, student.course_id) > 0
GROUP BY student.id, student.name