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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Thursday, October 27, 2011 7:21 AM
Cc: pgsql-general
Subject: Re: [GENERAL] 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@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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 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.