?
Solved

Convert many queries to 1 query

Posted on 2011-02-20
4
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 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