# Thread: Gettin COUNT from 2 different tables?

1. ## 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!

2. Originally Posted by jmansa
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. Originally Posted by jmansa
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```

4. Originally Posted by r937
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. Originally Posted by jmansa
...I can see I left out an important info in my description.
actually, more than one

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)

#### Posting Permissions

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