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? — Josh Greifer
– Josh Greifer, Commented Jul 31, 2017 at 8:58

RealCheeseLord · Accepted Answer · 2017-07-31 08:54:30Z

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, 2017 at 8:54

RealCheeseLord

7951 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bruno Over a year ago

Thanks, this however only returns one student. But I think I understand the concept.

RealCheeseLord Over a year ago

maybe add GROUP BY a.id

Josh Greifer · Accepted Answer · 2017-07-31 08:50:46Z

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, 2017 at 8:50

Josh Greifer

3,24127 silver badges26 bronze badges

1 Comment

Bruno Over a year ago

Thank you, i'll have a look at GROUP_CONCAT()

Collectives™ on Stack Overflow

Getting multiple rows as a single row in MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related