SQL query of multiple values in one cell

Question

There is a table(Course Interests) which has all the values in one cell. But those values are just ids and I want to join them with another table(Course) so I can know their names.

Course Interests:

MemberID          MemberName              CoursesInterested
--------------    ---------------------   --------------
1                  Al                     1,4,5,6
2                  A2                     3,5,6

Course Table:

CourseId          Course
--------------    ---------------------
1                 MBA 
2                 Languages
3                 English
4                 French
5                 Fashion
6                 IT

Desired Output:

MemberID          MemberName              CoursesInterested
--------------    ---------------------   --------------
1                  Al                     MBA,French,Fashion,IT
2                  A2                     English,Fashion,IT

I would like to do a SQL query in MySql that can help me to extract the desired output. I know how to do it in the opposite way(join values to one cell), but I've struggling on seek a way to separate the ids and do a cross-join into another table.

I'll appreciate any help from the community. Thanks

Answer 1

Use FIND_IN_SET to search for something in a comma-delimited list.

SELECT i.MemberID, i.MemberName, GROUP_CONCAT(c.Course) AS CoursesInterested
FROM CourseInterests AS i
JOIN Course AS c ON FIND_IN_SET(c.CourseId, i.CoursesInterested)

However, it would be better to create a relation table instead of storing the courses in a single column. This type of join cannot be optimized using an index, so it will be expensive for a large table.

Answer 2

Try this Out:

SELECT MemberID,MemberName,Group_Concat(C.Course) from
(
  SELECT MemberID,MemberName,SUBSTRING_INDEX(SUBSTRING_INDEX(t.CoursesInterested, ',', n.n), ',', -1) value
  FROM Table1 t CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n 
   ) n
 WHERE n.n <= 1 + (LENGTH(t.CoursesInterested) - LENGTH(REPLACE(t.CoursesInterested, ',', '')))
 ORDER BY MemberID,value
  ) T JOIN course C ON T.value = C.CourseId
Group By MemberID,MemberName

Fiddle Demo

Output:

---

MemberID          MemberName              CoursesInterested
--------------    ---------------------   --------------
1                  Al                     MBA,French,Fashion,IT
2                  A2                     English,Fashion,IT