Find in set to get the separate values with comma

Question

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.

*I am using below code* You went the right way, but stopped halfway. Add GROUP BY and GROUP_CONCAT(). — Akina, Dec 15 '21 at 07:33
@Akina Thanks for your comment. Can you show me sample how to add group by and group_concat() based on my question? — David Holly, Dec 15 '21 at 07:35
Ok. Now the problem cannot show value with comma like "1 David English,Science" — David Holly, Dec 15 '21 at 07:39

ProDec · Accepted Answer · 2021-12-15T07:41:34.797

2

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

edited Dec 15 '21 at 07:41

answered Dec 15 '21 at 07:34

ProDec

5,390
1
3
12

Thanks. But how to show value with comma like "1 David English,Science" – David Holly Dec 15 '21 at 07:38
@DavidHolly ??? your tell *I want the actual result is like below* - and the 1st query gives such output precisely. – Akina Dec 15 '21 at 07:43
To preserve the order of the subjects you want (untested) `GROUP_CONCAT(subject.subject ORDER BY FIND_IN_SET(subject.id, student.course_id))` – ysth Dec 15 '21 at 08:33

Find in set to get the separate values with comma

1 Answers1