Re: Saving score of 3 players into a table

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Wednesday, October 26, 2011 4:16 PM
Cc: pgsql-general
Subject: Re: [GENERAL] Saving score of 3 players into a table

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

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--------- /Original Message ---------------

SELECT *
FROM pref_scores
WHERE gid IN ( SELECT gid FROM pref_games WHERE id = 'DE9411' );

-- Note, "EXISTS" may be faster for large datasets but "IN", at least to me,
is much easier to understand.

In this case you want all scores for games in which person 'DE9411' was a
player.

David J.