The real problem here is that the team_id field in the team_member_scores table is extraneous. It was leading you down the garden path!
WHERE, though, did the field
member_name come from? In what you just showed me, there is no such field? Now you seem to be using
username instead? I'll go with that, but...
May I try completely rewriting the query, then?
Code:
$sql = "
SELECT M.username, M.stage, S.points
FROM team_members AS M
LEFT JOIN team_member_scores AS S ON M.username = S.username
WHERE M.team_id = $team_id AND M.game_id = $game_id_info
ORDER BY points DESC LIMIT 5";
(You don't need to use string concatenation with the period; PHP allows you to drop PHP variables into the middle of a quoted string so long as your use "..." instead of '...' for the quotes.)
Now, that will *NOT* get you the SUM of points for each team_member. That will just show you who scored the most points a SINGLE GAME.
Since you have limited the query to just one
game_id that's probably okay.
Variations on that:
Code:
$sql = "
SELECT M.username, M.stage, SUM(S.points) AS totalPoints
FROM team_members AS M
LEFT JOIN team_member_scores AS S ON M.username = S.username
WHERE M.team_id = $team_id
GROUP BY M.username, M.stage
ORDER BY points DESC LIMIT 5";
And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that
AND M.game_id = ... here. Note also that both fields are needed in the GROUP BY, as shown.)