1 more left join problem :(

[miz]

Originally Posted by Dean C

miz I'm finding it difficult to understand what you're trying to accomplish here. Can you just spend a couple of minutes thinking through about what you're asking and try and structure your request in a way we can understand it better. Thanks

ok ill try to explain
i want my php page will show list of all teams on board
then
take all users on $teamid and count there posts
then update teamposts in table teams

this is the main idea

now this query

PHP Code:

 $teamslist = $DB_site->query("
     SELECT teams.*,user.username AS username,
      user.userid AS userid ,user.posts AS userposts,
      user.reputation AS userrep,
     COUNT(user.posts) AS totalteamposts,
     COUNT(user.reputation) AS totalteamrep
         FROM " . TABLE_PREFIX . "teams AS teams
      LEFT JOIN user  ON(teams.teamid = user.teamid)
     WHERE teams.teamid > 1
     GROUP BY teams.teamid
     
 "); 


doing all of what i need except the db update
now my qustion is :

is there any way to combine the UPDATE of teams table with the query above
now the easy way to do update is on the while loop
but its mean the script will make 1 exstra query for each team
so if i got 100 teams on board it will make 100 querys more then should be

in other words, i want to update all teams info in 1 query
how i can do that ?