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.

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.


select SUM(score), player_id FROM games GROUP BY player_id WHERE id in (
  select id FROM games WHERE player_id = 1
)

let’s say you are player 1


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:


$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.

actually, more than one :slight_smile:

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)