-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber Sent: Thursday, October 27, 2011 7:21 AM Cc: pgsql-general Subject: Re: Saving score of 3 players into a table Thank you Michal and others - On Wed, Oct 26, 2011 at 11:11 PM, Michael Glaesemann <grzm@xxxxxxxxxxxxxxx> wrote: > 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; > I don't know what kind of JOIN that is (above) - but it works well: # 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 = 'DE9411'; gid | rounds | finished | id | money | quit ------+--------+----------------------------+------------------------+------ -+------ 43 | 12 | 2011-10-26 14:57:54.045975 | OK510649006288 | -240 | f 43 | 12 | 2011-10-26 14:57:54.045975 | DE9411 | 64 | f 43 | 12 | 2011-10-26 14:57:54.045975 | OK355993104857 | 176 | f 159 | 19 | 2011-10-26 15:55:54.650444 | DE9396 | 70 | f 159 | 19 | 2011-10-26 15:55:54.650444 | DE9411 | -110 | f 159 | 19 | 2011-10-26 15:55:54.650444 | OK5409550866 | 42 | f 224 | 16 | 2011-10-26 16:27:20.996753 | DE9396 | 4 | f 224 | 16 | 2011-10-26 16:27:20.996753 | DE9411 | 66 | f 224 | 16 | 2011-10-26 16:27:20.996753 | OK5409550866 | -70 | f 297 | 20 | 2011-10-26 17:05:53.514124 | OK486555355432 | -114 | f 297 | 20 | 2011-10-26 17:05:53.514124 | DE9411 | -36 | f 297 | 20 | 2011-10-26 17:05:53.514124 | OK5409550866 | 148 | f 385 | 20 | 2011-10-26 17:43:44.473597 | OK486555355432 | 245 | f 385 | 20 | 2011-10-26 17:43:44.473597 | DE9411 | 29 | f 385 | 20 | 2011-10-26 17:43:44.473597 | OK5409550866 | -275 | f 479 | 19 | 2011-10-26 18:26:05.00712 | OK486555355432 | 30 | f 479 | 19 | 2011-10-26 18:26:05.00712 | DE9411 | -40 | f 479 | 19 | 2011-10-26 18:26:05.00712 | OK5409550866 | 8 | f but now I'm lost even more - how to JOIN this with the pref_users table containing first_name, city for each player: # select first_name, female, avatar, city from pref_users where id = 'DE9411'; first_name | female | avatar | city ------------+--------+-----------------------------+---------- GRAF63 | f | picture-9411-1299771547.jpg | ALCORCON I'm trying: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) JOIN pref_users i USING (id) WHERE u.id = 'DE9411'; ERROR: common column name "id" appears more than once in left table Another try: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g, pref_users i JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = 'DE9411' and p.id=i.id; ERROR: column "gid" specified in USING clause does not exist in left table Regards 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 --------- A) Read the documentation on JOINs until you understand what is going on in the first query (specifically, how do "ON, NATURAL, USING" relate to each other and to the JOIN itself) B) Avoid mixing "JOIN" syntax with multiple tables in the "FROM" clause C) If you are getting ambiguity in columns you either need to force a JOIN order (using parentheses) OR revert to using explicit "ON ()" clauses Note, the "column gid ..." error above result because the planner is trying to join "pref_users AND pref_scores" but pref_users does not have a GID column to join on. It's as if you wrote: ( ( pref_games JOIN (pref_users JOIN pref_scores) ) JOIN pref_scores ) David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
