jmansa — 2012-07-21T18:30:50-04:00 — #1
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.
markbrown4 — 2012-07-21T20:07:46-04:00 — #2
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
r937 — 2012-07-21T21:22:32-04:00 — #3
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
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
BY playa_points DESC LIMIT 1
jmansa — 2012-07-22T09:07:30-04:00 — #4
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.
Actually I need to do this first:
$sql = mysql_query("SELECT *
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.
r937 — 2012-07-22T14:55:13-04:00 — #5
actually, more than one
starlion — 2012-07-23T08:13:06-04:00 — #6
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)