List all teams and team member details in a subject-CodePudding

I'm having some trouble working out how to write a query. I want to be able to make a list of all teams, and the students within the team that are in a certain subject.

Here are the sorts of tables I have.

Team (teamID, teamName, subjectID)
Student(studentID, Student Name)
AssignTeam(AssignID, studentID, teamID)
Subject(subjectID, subjectName)

This is what I would like the output to look like.

Team 1 - Team Name
Student 1 ID - Student 1 Name 
Student 2 ID - Student 2 Name 
Student 3 ID - Student 3 Name 
Student 4 ID - Student 4 Name 

Team 2 - Team Name
Student 1 ID - Student 1 Name 
Student 2 ID - Student 2 Name 
Student 3 ID - Student 3 Name 
Student 4 ID - Student 4 Name
Student 5 ID - Student 5 Name

Team 3 - Team Name
Student 1 ID - Student 1 Name 
Student 2 ID - Student 2 Name 
Student 3 ID - Student 3 Name

I'm struggling to work out how to format it in such a way to include the breaks between the groups themselves. I only really know how to make a list that looks like this

Group   StuID   StuName
--      ----    ----
1       1       Mike
1       2       Stacey
1       3       Jenny   
2       4       Rick
2       5       Sam
3       6       Larry
3       4       Anita

I want to build the list using mySQL but it will ultimately be outputting via PHP. I was hoping to create a stored procedure which I can then call and pass the subjectID into which will then create the list.

I haven't quite worked out if the one procedure would create the list and convert to string for output or if that should be two separate queries.

Any suggestions would be much appreciated.

Thanks

CodePudding user response：

This is not a mysql solution but would it not be simple enough to read the rows from mysql in PHP, and when the Group changes output the header line for that group?
See the snippet below. You'll have to make outputRow() and outputGroupHeader() generate the output in the format you need.

<?php
$lastgroup = "";
while ($row = mysqli_fetch_assoc($query))
{
  if ($row['group'] != $lastgroup) {
    outputGroupHeader($row['group']);
    $lastgroup = $row['group'];
  }
  outputRow($row);
}