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(at)seespotcode(dot)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