table JOIN and SUM help

[lukep11a]

Hi, I have 2 tables, one called 'selections' which stores the username and 12 teams which they have selected, the other table is called 'teams' which stores all 92 teams and the number of points they have. I am trying to display a table/list with every username and the total number of points they currently have from the 12 teams they selected. Here is my code:

 

<?php

	$query = "SELECT username, SUM(teams.points) FROM selections, teams WHERE (selections.group1 = teams.team OR selections.group2 = teams.team OR selections.group3 = teams.team OR selections.group4 = teams.team OR selections.group5 = teams.team OR selections.group6 = teams.team OR selections.group7 = teams.team OR selections.group8 = teams.team OR selections.group9 = teams.team OR selections.group10 = teams.team OR selections.group11 = teams.team OR selections.group12 = teams.team)";

	$result = mysql_query($query) or die(mysql_error());

	while($row = mysql_fetch_assoc($result))
	{
		echo $row['username']. " Total Points - ".$row['SUM(teams.points)'];
		echo "<br />";
      	}

 	?>

 

It currently only displays the first username entry and the total points is wrong at present. Does anyone know how I can modify the above code to generate the required output? Any help would be much appreciated. Thanks

[mikosiko]

you have a "spreadsheet" kind of design for your table Selections... which is really bad... you must reconsider that design, and your query (or the future ones) will be much easier .... maybe:

Selections

user_id,                        // FK to your table users.....(JOIN) against it to get the username

team                            // FK to your table teams

 

work on that...