Get a comma separated field from different table

Question

Having a student and subject table. Is it possible that by only using MySQL I can combine the values in one field as comma separated.

Student table

sid | subject_id
 1  |   1,2
 2  |   1
 3  |   2
 4  |   1,2,3

Subject Table

   subject_id | subject_name
        1     |   Maths
        2     |   Sci
        3     |   Eng
        4     |   Eco

Required Result

        sid   | subject_name
        1     |   Maths,Sci
        2     |   Maths
        3     |   Sci
        4     |   Maths,Sci,Eng

Code I tried

SELECT
  student_table.sid,
  GROUP_CONCAT(
    subject_table.subject_name
  )
FROM
  student_table
LEFT JOIN
  subject_table ON student_table.subject_id = subject_table.subject_id

don't put multiple values into a column. have sid multiple times for each subject in your student table. so a `(1,1), (1,2)...` should exist. — danblack, Jul 06 '20 at 07:39
lets say i dont have control on database i just read the data — Xyaa, Jul 06 '20 at 07:40
the use of comma separated values is based on bad db design concept .. you should avoid .. — ScaisEdge, Jul 06 '20 at 07:41
dear @danblack i am not in that level were i can ask the company to change the structure of db right now. — Xyaa, Jul 06 '20 at 07:52
You should take a note of their expertise and find a different employer. If they aren't one to recognize faults or improve basic practices from whatever level they come from they are unworthy of you time. If you aren't willing to raise it you aren't doing your job. — danblack, Jul 06 '20 at 07:55

score 0 · Answer 1 · answered Jul 06 '20 at 07:51

0

You can use the SUBSTRING_INDEX in the select and also use the CHAR_LENGTH on your join condition. Sample code

answered Jul 06 '20 at 07:51

Thilakar Raj

37
13

score 0 · Answer 2 · answered Jul 06 '20 at 07:59

Please try this:

SELECT student.id, GROUP_CONCAT(subject.subject_name)
FROM student
LEFT JOIN subject ON FIND_IN_SET(subject.subject_id, student.subject_id)
GROUP BY student.id

All you need to have is the function "FIND_IN_SET", for joining the tables.

Get a comma separated field from different table

2 Answers2