By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table faculty (
subject varchar(50)
);
create table subjects (
code varchar(50),
subject varchar(50)
);
insert into faculty values('101,102');
insert into faculty values('104');
insert into faculty values('103,105');
insert into subjects values('101', 'subject 1');
insert into subjects values('102', 'subject 2');
insert into subjects values('103', 'subject 3');
insert into subjects values('104', 'subject 4');
insert into subjects values('105', 'subject 5');
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;
| subject | GROUP_CONCAT(t2.subject) |
|---|---|
| 101,102 | subject 1,subject 2 |
| 103,105 | subject 3,subject 5 |
| 104 | subject 4 |