Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert many queries to 1 query

Posted on 2011-02-20
4
Medium Priority
?
237 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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

609 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