SQL: GROUP BY after JOIN without overriding rows?
- by krismeld
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?
