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.
I'm trying (these are all games played by DE9411):
# select * from pref_scores where id='DE9411';
id | gid | money | quit
--------+-----+-------+------
DE9411 | 43 | 64 | f
DE9411 | 159 | -110 | f
DE9411 | 224 | 66 | f
DE9411 | 297 | -36 | f
DE9411 | 385 | 29 | f
DE9411 | 479 | -40 | f
DE9411 | 631 | -14 | f
DE9411 | 699 | 352 | f
DE9411 | 784 | -15 | f
DE9411 | 835 | 242 | f
Then I'm trying to join with pref_games:
# select s.id, s.gid, s.money, s.quit, to_char(g.finished,
'DD.MM.YYYY') as day from pref_scores s, pref_games g where
s.gid=g.gid and s.id='DE9411';
id | gid | money | quit | day
--------+-----+-------+------+------------
DE9411 | 43 | 64 | f | 26.10.2011
DE9411 | 159 | -110 | f | 26.10.2011
DE9411 | 224 | 66 | f | 26.10.2011
DE9411 | 297 | -36 | f | 26.10.2011
DE9411 | 385 | 29 | f | 26.10.2011
DE9411 | 479 | -40 | f | 26.10.2011
DE9411 | 631 | -14 | f | 26.10.2011
DE9411 | 699 | 352 | f | 26.10.2011
DE9411 | 784 | -15 | f | 26.10.2011
DE9411 | 835 | 242 | f | 26.10.2011
But how do I display the 2 other players and
their scores in the above result set?
(More info on my problem:
http://stackoverflow.com/questions/7899995/save-scores-of-3-players-per-game-into-postgresql
)
Thank you
Alex
