SQL: GROUP BY after JOIN without overriding rows?

Question

I have a table of basketball leagues, a table af teams and a table of players like this:

LEAGUES
ID   |   NAME    |
------------------
1    |   NBA     |
2    |   ABA     |

TEAMS:
ID   |   NAME    |  LEAGUE_ID
------------------------------
20   |   BULLS   |    1
21   |   KNICKS  |    2

PLAYERS:
ID   |   TEAM_ID  |  FIRST_NAME | LAST_NAME | 
---------------------------------------------
1    |      21    |   John      |  Starks   |    
2    |      21    |   Patrick   |  Ewing    |    

Given a League ID, I would like to retrieve all the players' names and their team ID from all the teams in that league, so I do this:

SELECT t.id AS team_id, p.id AS player_id, p.first_name, p.last_name
FROM teams AS t
JOIN players AS p ON p.team_id = t.id
WHERE t.league_id =  1

which returns:

[0] => stdClass Object
    (
        [team_id] => 21
        [player_id] => 1
        [first_name] => John
        [last_name] => Starks
    )

[1] => stdClass Object
    (
        [team_id] => 21
        [player_id] => 2
        [first_name] => Patrick
        [last_name] => Ewing
    )

+ around 500 more objects...

Since I will use this result to populate a dropdown menu for each team containing each team's list of players, I would like to group my result by team ID, so the loop to create these dropdowns will only have to cycle through each team ID instead of all 500+ players each time.

But when I use the GROUP BY like this:

SELECT t.id AS team_id, p.id AS player_id, p.first_name, p.last_name
FROM teams AS t
JOIN players AS p ON p.team_id = t.id
WHERE t.league_id =  1
GROUP BY t.id

it only returns one player from each team like this, overriding all the other players on the same team because of the use of the same column names.

[0] => stdClass Object
    (
        [team_id] => 21
        [player_id] => 2
        [first_name] => Patrick
        [last_name] => Ewing
    )
[1] => stdClass Object
    (
        [team_id] => 22
        [player_id] => 31
        [first_name] => Shawn
        [last_name] => Kemp
    )
etc...

I would like to return something like this:

[0] => stdClass Object
    (
        [team_id] => 2

        [player_id1] => 1
        [first_name1] => John
        [last_name1] => Starks
        [player_id2] => 2
        [first_name2] => Patrick
        [last_name2] => Ewing

        +10 more players from this team...
    )

    +25 more teams...

Is it possible somehow?

Answer 1

You cannot do this in SQL, since you cannot represent that result in a form of data set. You want to return complex object. What you can do, is to handle this in the code, and help yourself by returning a data set which is sorted by team_id. Whenever your team_id changes, then it is time to create new object in your code and fill it with new list of players.

It would be something like this (syntax might not be correct):

Returned result set:

team_id|player_id|first|last
1|1|f1|l1
1|2|f2|l2
1|3|f3|l3
2|5|f5|l5
2|6|f6|l6

And when this is returned in your code

$lastTeamId=0;
$output=array();
foreach($results as $row){
  if($lastTeamId != $row["team_id"]){
    $lastTeamId = $row["team_id"];
    $output[$lastTeamId] = array();
  }

  $newPlayer = null;
  $newPlayer->id = $row["player_id"];
  $newPlayer->first = $row["first"];
  $newPlayer->last = $row["last"];
  $output[$lastTeamId][] = $newPlayer;
}