How to select row/s from an other table using a comma-separated value from the first table? [duplicate]

Question

Is there any way to select row/s from an other table using a comma-separated value from the first table?

Table 1. faculty

subject
101, 102
104
103, 105

Table 2. subject

code	subject
101	subject 1
102	subject 2
103	subject 3
104	subject 4
105	subject 5

Expected Output:

subject	subject
101, 102	subject 1, subject 2
104	subject 4
103, 105	subject 3, subject 5

I already tried this:

SELECT faculty.subject, subject_offered.code, subject_offered.subject 
       FROM faculty 
       LEFT JOIN subject_offered 
       ON subject_offered.code 
       IN (faculty.subject)

but the rows from faculty with multiple values (comma-separated) shows NULL in the code and subject column in subject table.

my output

Answer 1

SELECT t1.subject, GROUP_CONCAT(t2.subject)
FROM faculty t1
JOIN subjects t2 ON FIND_IN_SET(t2.code, t1.subject)
GROUP BY t1.subject;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3e40ab353ff1e0a1cde678225fa63ed2

Answer 2

Updated

Please see the answer by @Akina for a complete correct and cleaner solution.

Although this is a very poor table design, there's still a way to get around this.

Solution

SELECT faculty_subject AS faculty_subject_codes, GROUP_CONCAT(subject ORDER BY code ASC) AS subjects
FROM (
  SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%')
) fa GROUP BY faculty_subject;

Give

faculty_subject_codes	subjects
101,102	subject 1,subject 2
103,105	subject 3,subject 5
104	subject 4

Checkout my fiddle for your question

Explanation

Since the faculty.subject is a comma separated values, so you cannot do a regular join by subject.code, the work around is to use a JOIN with LIKE clause instead.

SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%');

faculty_subject	code	subject
101,102	101	subject 1
101,102	102	subject 2
103,105	103	subject 3
104	104	subject 4
103,105	105	subject 5

Now we have multiple rows with the same faculty_subject , e.g for code 103 and 105. The next thing is to combine those duplicated row into a single entry, we will do this with a GROUP CONCAT statement, and GROUP BY the faculty_subject

SELECT faculty_subject AS faculty_subject_codes, GROUP_CONCAT(subject ORDER BY code ASC) AS subjects
FROM (
  SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%')
) fa GROUP BY faculty_subject;