asked on

Convert many queries to 1 query

I was wondering if anyone saw a way of combining what is happening below into one query to help improve performance?

$sql = " select * from Team Inner Join Competition on Team.CompetitionIDAuto = Competition.CompetitionIDAuto where Competition.CompetitionIDAuto = $CompetitionIDAuto and CompetitionDisabled = 0";
$result = mysql_query($sql,$db);
while ($myrow=mysql_fetch_array($result))
{
	$TeamIDAuto = $myrow["TeamIDAuto"];

	$sqlpoints = "select IFNULL(sum(Points),0) as 'Points' from TeamTransfers where TeamIDAuto = $TeamIDAuto";
	$resultpoints = mysql_query($sqlpoints,$db);

	while ($myrowpoints=mysql_fetch_array($resultpoints))
	{
		$Points = $myrowpoints["Points"];
	}
	mysql_free_result($resultpoints);

	$TotalTeamPoints = $myrow["Opener1Points"] + $myrow["Opener2Points"] + $myrow["Bat1Points"] + $myrow["Bat2Points"] + $myrow["Bat3Points"] + $myrow["WKPoints"] + $myrow["AR1Points"] + $myrow["Bowl1Points"] + $myrow["Bowl2Points"] + $myrow["Bowl3Points"] + $myrow["Bowl4Points"] + $Points;

	$sqlupdate = " Update Team Set TotalTeamPoints = $TotalTeamPoints where TeamIDAuto = $TeamIDAuto";
	mysql_query($sqlupdate,$db);

}
mysql_free_result($result);

Open in new window

PeterErhard

ASKER

I've got this far which is I believe is right but having trouble trying to associate the update with the sub query. Any help there would be great.

Update Team
Set TotalPoints = a.CalculatedPoints
(
SELECT Team.TeamIDAuto, (
Opener1Points + Opener2Points + Bat1Points + Bat2Points + Bat3Points + WKPoints + AR1Points + Bowl1Points + Bowl2Points + Bowl3Points + Bowl4Points + IFNULL( SUM( Points ) , 0 )
) AS CalculatedPoints
FROM Team
LEFT JOIN TeamTransfers ON Team.TeamIDAuto = TeamTransfers.TeamIDAuto
WHERE Team.CompetitionIDAuto =289
GROUP BY Team.TeamIDAuto
) a

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterErhard

ASKER

I ended up writing the following query in the end.

I'll give you the points because yours works as well.

Which query is better do you think?

Update Team
Inner Join
(
SELECT Team.TeamIDAuto, (
Opener1Points + Opener2Points + Bat1Points + Bat2Points + Bat3Points + WKPoints + AR1Points + Bowl1Points + Bowl2Points + Bowl3Points + Bowl4Points + IFNULL( SUM( Points ) , 0 )
) AS CalculatedPoints
FROM Team
LEFT JOIN TeamTransfers ON Team.TeamIDAuto = TeamTransfers.TeamIDAuto
WHERE Team.CompetitionIDAuto = " . $CompetitionIDAuto;
GROUP BY Team.TeamIDAuto
) a 
on Team.TeamIDAuto = a.TeamIDAuto
Set TotalTeamPoints = a.CalculatedPoints

Open in new window

tigin44

my query outperforms yours..