SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Gettin COUNT from 2 different tables?

    I'm trying to figure out how to find out which player in a game db able I have wont the most games against. I have a db table in which I have 2 rows for each game. One for me and one for the opponent. When a game is finished the player who has won is awarded some +points and the loser is given -points. So as far as i can see I have to go through the table and find all the games I have got +points. But that doesn't give me the opponents ID and count of how many I have won!

    My table looks like this:
    id fk_game_id fk_player_id points

    So lest say I have player id 1 a won game would look like this:
    1 1 1 23
    2 1 2 -23

    So how do I get to echo out the player I have won most game against? I have tried several of solutions with GROUP and JOINS but I got it all messed up now so I won't even post this.

    Can anyone point me in the right direction!

    Thanks in advance.

  2. #2
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,107
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jmansa View Post
    So how do I get to echo out the player I have won most game against? I have tried several of solutions with GROUP and JOINS but I got it all messed up now so I won't even post this.
    SQL's really not my strength but perhaps something like this will work?
    Find all the game_id's you've played in.
    Then get the SUM of the scores grouped by player_ids, unique to those games.
    Code sql:
    SELECT SUM(score), player_id FROM games GROUP BY player_id WHERE id IN (
      SELECT id FROM games WHERE player_id = 1
    )

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    So how do I get to echo out the player I have won most game against?
    let's say you are player 1
    Code:
    SELECT opponent.fk_player_id AS opponent_id
         , SUM(playa.points)     AS playa_points
      FROM games AS playa
    INNER
      JOIN games AS opponent
        ON opponent.fk_game_id = playa.fk_game_id
       AND opponent.fk_player_id <> playa.fk_player_id
       AND opponent.points < playa.points
     WHERE playa.fk_player_id = 1 -- you are da playa
    GROUP
        BY opponent.fk_player_id
    ORDER
        BY playa_points DESC LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    let's say you are player 1
    Code:
    SELECT opponent.fk_player_id AS opponent_id
         , SUM(playa.points)     AS playa_points
      FROM games AS playa
    INNER
      JOIN games AS opponent
        ON opponent.fk_game_id = playa.fk_game_id
       AND opponent.fk_player_id <> playa.fk_player_id
       AND opponent.points < playa.points
     WHERE playa.fk_player_id = 1 -- you are da playa
    GROUP
        BY opponent.fk_player_id
    ORDER
        BY playa_points DESC LIMIT 1
    This doesn't do the trick. It does another trick, but I can see I left out an important info in my description. I want to count how many games I have played against each player, then figure out how many of those I won (when I have +points), and then figure out which player I have won the most games against or which player I have the highest procentage wain against. Sorry for the lack of info.

    EDIT:

    Actually I need to do this first:
    Code:
    $sql = mysql_query("SELECT *
    						FROM ".$prefix."_gameplayer
    					WHERE fk_player_id = 1 AND player_points > 0");
    Then find the opponent and then figure out which player I have won most times against.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    ...I can see I left out an important info in my description.
    actually, more than one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    why would you have two rows for a single game's result? (reason i ask is that it makes it more difficult to do the "which games did i win" part)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


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
  •