I want to make a table of lessons and student marks.
Lessons;
ID | Lesson_Name | Teacher_Name
---------------------------------
1 | Maths | Teacher1
2 | Literature | Teacher2
3 | Psychology | Teacher3
4 | Law | Teacher4
Student_Marks;
ID | LessonID | Student ID | Mark
---------------------------------
1 | 1 | 1 | 100
2 | 1 | 2 | 70
3 | 2 | 1 | 80
4 | 3 | 2 | 30
5 | 2 | 3 | 60
Students;
ID | Student
---------------------------------
1 | John
2 | Ashley
3 | Geroge
4 | Steve
I want a table like this one;
Lesson | Teacher | Student1 | Student2 | Student3
-------------------------------------------------------
Maths | Teacher1 | 100 | 70 | 60
Literature | Teacher2 | 70 | 40 |
Psychology | Teacher3 | | 50 | 30
Law | Teacher4 | 30 | 60 |
Currently I get all data with the query below and parsing them with PHP to build the table.
SELECT
l.id,l.lesson_id,l.lesson_name,l.teacher_name,
GROUP_CONCAT(CONCAT_WS(':', sm.studentid, sm.studentmark) SEPARATOR ',') as students
FROM lessons l
LEFT JOIN student_marks sm ON l.lesson_id = sm.lesson_id
GROUP BY l.lesson_id
Is there a more efficient way?