Solved

Convert many queries to 1 query

Posted on 2011-02-20
4
227 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:PeterErhard
  • 2
  • 2
4 Comments
 

Author Comment

by:PeterErhard
Comment Utility
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
0
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
Comment Utility
try this
$sql = " UPDATE Team
	SET Team.TotalTeamPoints = A.Points  
	FROM Team
		INNER JOIN 
		(select Team.TeamIDAuto, SUM(IFNULL(sum(Points),0)) AS Points  
		from Team 
			Inner Join Competition on Team.CompetitionIDAuto = Competition.CompetitionIDAuto 
			Inner Join TeamTransfers ON TeamTransfers.TeamIDAuto = Team.TeamIDAuto  
		where Competition.CompetitionIDAuto = $CompetitionIDAuto and CompetitionDisabled = 0
		GROUP BY Team.TeamIDAuto) A ON A.TeamIDAuto = Team.TeamIDAuto";
mysql_query($sql,$db);

Open in new window

0
 

Author Comment

by:PeterErhard
Comment Utility
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

0
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
my query outperforms yours..
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now