SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help me on this mySQL query?

    Ok, I have two tables. One is km_users, one is km_teams

    km_users has a field called power, km_teams has field called teampower.

    km_users has a field called team whose value is the same as the teamID field of km_teams.

    So I want a query to run where it updates teach team of km_teaches with the total sum of the power of all the users that has the same teamID in the teamID field.

    I know a crappy way to do it like this:
    Code:
    $getteams="SELECT * from km_teams";
    $getteams2=mysql_query($getteams) or die("Could not get teams");
    while($getteams3==mysql_fetch_array($getteams2))
    {
    	//select km_users with team id
       //while loop to add together all the power values
      //update field
    }
    This way, there could get to be alot of queries if there are many teams. If there a better way to do it?

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     select t.teamID, sum(u.power) teampower
    from km_teams t, km_users u
    where u.team = t.teamID
    grabs all the teams power in a single query, but I don't think you can issue the update as a single statement in a database which does not support sub-select (which MySQL did not the last time I checked).
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As long as I can display them as teampoer DESC, its fine.
    So can I:
    Code:
    select t.teamID, sum(u.power) teampower from km_teams t, km_users u where u.team = t.teamID order by teampower DESC


    ?

  4. #4
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Should work fine, might have to be "order by 2 desc".

  5. #5
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Order by 2 DESC?

  6. #6
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2 = second column in your select clause

  7. #7
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gave me this error:
    Code:
    Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yup, you need this --
    Code:
    select t.teamID
         , sum(u.power) teampower
      from km_teams t
    inner
      join km_users u
        on t.teamID 
         = u.team
    group
        by t.teamID
    order 
        by teampower desc
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, it works, you can tell I don't use left and inner joins much. Only a couple of times.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •