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.
>Solution :
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