Converting Comma Separated Data (CSV) data to rows in MySQL

It took me quite some time to actually find the solution for joining comma separated values column with a reference table. For this example, let say we have these 2 tables as show below. REF_SUBJECTS is the reference table for the list of subjects available for student to enroll with. STUDENT is the list of student with its enrolled subjects stored in one column in CSV format.

Therefore, we need to find a way to join REF_SUBJECTS.ID with STUDENT.SUBJECTS.

This can actually be achieved using this genius solution as shared in this SO thread :
https://stackoverflow.com/questions/19073500/sql-split-comma-separated-row

This is the original syntax of the SQL:

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
FROM
   table1 t
   CROSS JOIN tally n
WHERE
   n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', ''))) 
ORDER BY 
   value

Therefore, to use this for our example, we need to change it like so to get a list of subjects taken by our student, Jimmy :

SELECT 
   SUBSTRING_INDEX(SUBSTRING_INDEX(t.subjects, ',', n.id), ',', -1) as subjects_list
FROM
   mydb.student t
   CROSS JOIN mydb.ref_subjects n
WHERE
   n.id <= 1 + (LENGTH(t.subjects) - LENGTH(REPLACE(t.subjects, ',', ''))) 
   and t.id= 2

This will return 4 rows of data, which contains the subject ID of subjects taken by Jimmy.

I’m so happy this actually worked. My hats off to you.