Triple 'INNER JOIN' problem

[Mutley]

I'm guessing an Array might be best here but here is my code:

 

<?php

	$sql = "SELECT t.team_id, t.name, t.owner, t.sponsor, l.user_id, l.2playerpts, l.4playerpts, l.6playerpts, l.8playerpts, l.wins, l.losses, u.user_id, u.team_id
	FROM teams t
	INNER JOIN users u ON u.team_id = t.team_id
	INNER JOIN leaderboard l ON l.user_id = u.user_id
	ORDER BY (l.2playerpts + l.4playerpts + l.6playerpts + l.8playerpts) DESC";

	$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
	while (list($t_team_id, $t_name, $t_owner, $t_sponsor, $l_user_id, $l_2playerpts, $l_4playerpts, $l_6playerpts, $l_8playerpts, $l_wins, $l_losses, $u_user_id, $u_team_id) = mysql_fetch_row($res)) {
?>			
<tr>
	<td><?=$t_team_id?></td>
	<td><?=$t_name?></td>
	<td><? userProfile($t_owner); ?></td>
	<td><?=$t_sponsor?></td>
	<td><? echo $l_2playerpts + $l_4playerpts + $l_6playerpts + $l_8playerpts; ?></td>
	<td></td>
</tr>	
<?
}
?>
</table>

?>

 

What it is doing is listing Teams (from the `teams` table) then inner joining to find the users who are in that team (from `users` table, `team_id` field). It the calculates the users score in that team (from the `leaderboard` table) and orders it by the score.

 

The problem I have is, I want it to order it by the total score of all the users in the team. At the moment it lists the teams multiple times for each user in it and the users individual score along side it. Instead, I'd like it to group the Teams and display a total score of all the users in that Team.

 

Hope that makes sense.

 

Thanks in advance,

Nick.