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

user_17247342 0
Viewed 241

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

sql
3 Answers
user_101387340

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;
user_56689560 
    SELECT  f.subject,
            GROUP_CONCAT(s.subject  ) as subject
    FROM    faculty as f
    INNER JOIN subjects s
        ON FIND_IN_SET(s.code, f.subject) 
    GROUP BY f.subject;

Run in Fiddle

user_101174330
Related