Re: Saving score of 3 players into a table

On Oct 26, 2011, at 16:15, Alexander Farber wrote:

> Hello again,
>
> still I can't figure out how to perform a join
> to fetch all games where a player has participated -
> I have a table containing all games played:
>
> # select * from pref_games limit 5;
> gid | rounds | finished
> -----+--------+----------------------------
> 1 | 10 | 2011-10-26 14:10:35.46725
> 2 | 12 | 2011-10-26 14:34:13.440868
> 3 | 12 | 2011-10-26 14:34:39.279883
> 4 | 14 | 2011-10-26 14:35:25.895376
> 5 | 14 | 2011-10-26 14:36:56.765978
>
> And I have a table with scores of each of 3 players:
>
> # select * from pref_scores where gid=3;
> id | gid | money | quit
> -----------------------+-----+-------+------
> OK515337846127 | 3 | -37 | f
> OK40798070412 | 3 | -75 | f
> MR2871175175044094219 | 3 | 112 | f
>
> (Which means 3 players have played game #3
> and 1 has won 112, while 2 have lost 37 + 75)
>
> My problem is: I'd like to list all games played
> by 1 player, with all participants and scores listed.

Get games for a particular user:

SELECT g.gid, g.rounds, g.finished
FROM pref_games g
JOIN pref_scores u USING (gid)
WHERE u.id = :id;

Now, add the participants for those games

SELECT g.gid, g.rounds, g.finished,
p.id, p.money, p.quit
FROM pref_games g
JOIN pref_scores u USING (gid)
JOIN pref_scores p USING (gid)
WHERE u.id = :id;

Michael Glaesemann
grzm seespotcode net