MySQL Select from column use ^ as delimiter

Question

My question similar to MySQL Split String and Select with results. Currently I have 2 tables:

student

uid | subject_id | name
1   | 1^2^3^4    | a
2   | 2^3^       | b
3   | 1          | c

subject

uid | subject_name
1   | math
2   | science
3   | languange
4   | sport

The result I expected is:

uid | name    | subject_passed
1   | a       | math, science, languange, sport
2   | b       | science, languange
3   | c       | sport

I have tried this query:

SELECT
student.uid,
student.name, 
group_concat(subject.subject_name) as subjects_passed 
from student 
join subject on find_in_set(subject.uid,student.subject_id ) > 0
group by student.uid

Which returns the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join subject on find_in_set(subject.uid,student.subject_id ) > 0 group' at line 7

I believe because of FIND_IN_SET. According to documentation, this function expects , as delimiter. Is there any alternative query I might use ?

Answer 1

Why not REPLACE the separator:

SELECT
    student.uid,
    student.name,
    GROUP_CONCAT(subject.subject_name) AS subjects_passed
FROM student
JOIN subject ON FIND_IN_SET(subject.uid, REPLACE(student.subject_id, '^', ',')) > 0
GROUP BY student.uid

SQLFiddle

---

If you decide to de-normalize your tables it is fairly straight forward to create the junction table and generate the data:

-- Sample table structure
CREATE TABLE student_subject (
    student_id int NOT NULL,
    subject_id int NOT NULL,
    PRIMARY KEY (student_id, subject_id)
);

-- Sample query to denormalize student <-> subject relationship
SELECT
    student.uid AS student_id,
    subject.uid AS subject_id
FROM student
JOIN subject ON FIND_IN_SET(subject.uid, REPLACE(student.subject_id, '^', ',')) > 0

+------------+------------+
| student_id | subject_id |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          2 |
|          2 |          3 |
|          3 |          1 |
+------------+------------+

Answer 2

You should never store data with a delimiter separator and should normalize the table and create the 3rd table to store student to subject relation.

However in the current case you may do it as

select
st.uid,
st.name,
group_concat(sb.subject_name) as subject_name
from student st
left join subject sb on find_in_set(sb.uid,replace(st.subject_id,'^',',')) > 0
group by st.uid

Here is the option to create third table and store the relation

create table student_to_subject (id int primary key auto_increment, stid int, subid int);
insert into student_to_subject(stid,subid) values
(1,1),(1,2),(1,3),(1,4),(2,2),(2,3),(3,1);

Now you can remove the column subject_id from the student table So the query becomes

select
st.uid,
st.name,
group_concat(sb.subject_name) as passed_subject
from student st
join student_to_subject sts on sts.stid = st.uid
join subject sb on sb.uid = sts.subid
group by st.uid;

http://www.sqlfiddle.com/#!9/f02df