Solved

Convert many queries to 1 query

Posted on 2011-02-20
4
229 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
ID: 34936210
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
ID: 34936225
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
ID: 34936736
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
ID: 34936827
my query outperforms yours..
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

813 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

13 Experts available now in Live!

Get 1:1 Help Now