Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: 3 table join with 2 sum functions

    I have 3 tables that I need to join.

    Descriptions of tables
    player - Contains player's name and id
    game_player - Contains the player and what game they played in and for how long.
    weapon_t - Contains a table of what weapon what player used to kill anyone per game.

    DESCRIBE TABLES

    mysql> DESCRIBE player;
    +--------+-----------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +--------+-----------------------+------+-----+---------+----------------+
    | pid | mediumint(8) unsigned | | PRI | NULL | auto_increment |
    | name | varchar(40) | YES | | NULL | |
    | model | varchar(30) | YES | | NULL | |
    | rating | float | YES | | NULL | |
    +--------+-----------------------+------+-----+---------+----------------+

    mysql> DESCRIBE game_player;
    +-------------+-----------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+-----------------------+------+-----+---------+-------+
    | gid | mediumint(8) unsigned | | | 0 | |
    | pid | mediumint(8) unsigned | | | 0 | |
    | ptimeactive | mediumint(8) unsigned | YES | | NULL | |
    | pteam | varchar(30) | YES | | NULL | |
    +-------------+-----------------------+------+-----+---------+-------+

    mysql> DESCRIBE weapon_t;
    +----------+-----------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-----------------------+------+-----+---------+-------+
    | gid | mediumint(8) unsigned | | | 0 | |
    | pid | mediumint(8) unsigned | | | 0 | |
    | wid | tinyint(3) unsigned | | | 0 | |
    | kills | mediumint(8) unsigned | | | 0 | |
    | deaths | mediumint(8) unsigned | | | 0 | |
    | suicides | mediumint(8) unsigned | | | 0 | |
    +----------+-----------------------+------+-----+---------+-------+

    pid = player's id
    gid = game's id
    wid = weapon's id

    I want to make a table that has the following.
    player's name, player's id, total time active, total kills

    The best SQL command I got is the following.

    SELECT player.name, player.pid, SUM(weapon_t.kills) as kills, SUM(game_player.ptimeactive) AS timeactive FROM player LEFT JOIN game_player ON game_player.pid = player.pid LEFT JOIN weapon_t ON player.pid = weapon_t.pid GROUP BY player.pid


    This command though gives me inaccurate results for kills and timeactive. Probably from a doubling effect or something.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, a cross-join effect

    without subqueries (version 4.1) your best bet is to do something like this:
    PHP Code:
    select player.name
         
    player.pid
         
    'timeactive'                 as quantity
         
    sum(game_player.ptimeactive) as amount 
      from player 
    left outer 
      join game_player 
        on player
    .pid
         
    game_player.pid 
    group 
        by player
    .pid     
    union all     
    select player
    .name
         
    player.pid
         
    'kills'
         
    SUM(weapon_t.kills
      
    from player 
    left outer 
      join weapon_t 
        on player
    .pid
         
    weapon_t.pid
    group 
        by player
    .pid 
    if you're not on 4.0 you will have to run the two queries separately
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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