Getting multiple rows as a single row in MySQL

Question

I have a basic multi-school management system that I am working on for fun in order to learn querying from a MySQL database.

I am doing a number of joins from tables such as students, schools,student_grade, grade_school etc. I now want to get a list of all subjects the student has been assigned. Since there are multiple possible rows, I have a student_subject table to hold the student_id and then the subject_id values.

The following query gets me the student's name, and their grade:

SELECT a.id,a.firstnames,a.surname,d.gradename 
FROM students a 
LEFT JOIN student_grade b ON a.id=b.studentid
LEFT JOIN grade_school c ON c.id=b.gradeid
LEFT JOIN grades d ON c.gradeid=d.id
WHERE a.schoolid=? AND b.gradeid=? ORDER by a.surname ASC

This results in each row returned to be the student's name and their grade, like this:

ID   |   Firstname    |   Surname    |   Grade
 1   |     John       |      Doe     |     4
 5   |      Sarah     |   Marshall   |     7

How can I get all the subjects the student is assigned and put them all in a column "subject_ids" to get a result as follows:

ID   |   Firstname    |   Surname    |   Grade  |  Subjects
 1   |     John       |      Doe     |     4    |  5,54,2,56
 5   |      Sarah     |   Marshall   |     7    |  2,4,12,17

My table structures are as follows:

Table `students`
id | firstnames | surname | schoolid


Table `student_grade`
id | studentid | gradeid


Table `grade_school`
id | gradeid| schoolid


Table `grades`
id | gradename|


Table `student_subject`
id | studentid| subjectid
 4 |    1     |    5
 5 |    1     |    54
 6 |    1     |    2
 7 |    1     |    56
 8 |    5     |    2
 9 |    5     |    4
10 |    5     |    12
11 |    5     |    17

Possible duplicate of [Can I concatenate multiple MySQL rows into one field?](https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — Josh Greifer, Jul 31 '17 at 08:58

score 2 · Accepted Answer · answered Jul 31 '17 at 08:54

2

Try this:

SELECT a.id,a.firstnames,a.surname,d.gradename, GROUP_CONCAT(s.subjectid)
FROM students a 
LEFT JOIN student_grade b ON a.id=b.studentid
LEFT JOIN grade_school c ON c.id=b.gradeid
LEFT JOIN grades d ON c.gradeid=d.id
LEFT JOIN student_subject s ON s.studentid=a.id
WHERE a.schoolid=? AND b.gradeid=? ORDER by a.surname ASC

also check this or this

answered Jul 31 '17 at 08:54

RealCheeseLord

785
1
12
24

1

Thanks, this however only returns one student. But I think I understand the concept. – Bruno Jul 31 '17 at 09:00
maybe add `GROUP BY a.id` – RealCheeseLord Jul 31 '17 at 09:11

score 1 · Answer 2 · answered Jul 31 '17 at 08:50

1

The GROUP_CONCAT() function will do what you want here.

This question is a dup of Can I concatenate multiple MySQL rows into one field?

answered Jul 31 '17 at 08:50

Josh Greifer

3,151
24
25

Thank you, i'll have a look at GROUP_CONCAT() – Bruno Jul 31 '17 at 09:00

Getting multiple rows as a single row in MySQL

2 Answers2