Re: Saving score of 3 players into a table - Mailing list pgsql-general

Thank you Michal and others -

On Wed, Oct 26, 2011 at 11:11 PM, Michael Glaesemann
<grzm@seespotcode.net> 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