Re: Saving score of 3 players into a table

On Oct 27, 2011, at 7:21, Alexander Farber wrote:

> 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:

It's just a normal join. There's nothing special about it.

> 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

There are two id's: u.id, and p.id. You need to specify which one you're joining on with i:

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 ON i.id = p.id
WHERE u.id = 'DE9411';

> 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

This is complaining about
pref_users i
JOIN pref_scores u USING (gid)

i doesn't have a gid column.

Looks like you could use some work on basic SQL. I recommend picking up a basic SQL book.

Michael Glaesemann
grzm seespotcode net