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