-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber Sent: Wednesday, October 26, 2011 4:16 PM Cc: pgsql-general Subject: Re: 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@xxxxxxxxxxxxxx) 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
