MySQL Select from column use ^ as delimiter
My question is similar to MySQL Split String and select with results . I currently 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
Expected Result:
uid | name | subject_passed
1 | a | math, science, languange, sport
2 | b | science, languange
3 | c | sport
I 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 an error:
# 1064 - You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for correct use of the syntax near 'join subject on find_in_set (subject.uid, student.subject_id)> 0 group' on line 7
I believe because of FIND_IN_SET
. According to the documentation, this function expects ,
as a delimiter. Is there an alternative query that I could use?
source to share
Why not a REPLACE
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
If you decide to de-normalize your tables, it's pretty simple to create a join 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 | +------------+------------+
source to share
You should never store delimited data and should normalize the table and create a third table to store the student-to-subject relationship.
However, in the current case, you can do it like
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 you can create a third table and save 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 drop the column subject_id
from the table student
This way 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;
source to share
Think what you can replace ^
with ,
before calling find_in_set:
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
But of course storing values in this format is very bad db design.
source to share