Обсуждение: Saving score of 3 players into a table
Hello,
I'm trying to save results of card game with 3 players into a table.
It is bad enough, that I had to introduce
3 columns for user ids: id0, id1, id2 and
3 columns for their scores: money0, money1, money2 -
create table pref_results (
id0 varchar(32) references pref_users,
id1 varchar(32) references pref_users,
id2 varchar(32) references pref_users,
money0 integer not null,
money1 integer not null,
money2 integer not null,
rounds integer not null,
finished timestamp default current_timestamp
);
But now I've also realized, that I don't know,
how to join that table with the pref_users,
so that I get first_name for each of 3 players -
$sth = $db->prepare("
select
id0,
id1,
id2,
money0,
money1,
money2,
rounds,
to_char(finished,'DD.MM.YYYY') as day
from pref_results
where finished > now() - interval '1 week'
and (id0=? or id1=? or id2=?)
");
$sth->execute(array($id, $id, $id));
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
# XXX print the table with day, first_names and money
}
I'm probably doing something wrong here?
Thank you
Alex
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, October 25, 2011 3:33 PM
To: pgsql-general
Subject: [GENERAL] Saving score of 3 players into a table
Hello,
I'm trying to save results of card game with 3 players into a table.
It is bad enough, that I had to introduce
3 columns for user ids: id0, id1, id2 and
3 columns for their scores: money0, money1, money2 -
create table pref_results (
id0 varchar(32) references pref_users,
id1 varchar(32) references pref_users,
id2 varchar(32) references pref_users,
money0 integer not null,
money1 integer not null,
money2 integer not null,
rounds integer not null,
finished timestamp default current_timestamp
);
But now I've also realized, that I don't know, how to join that table with
the pref_users, so that I get first_name for each of 3 players -
[...]
I'm probably doing something wrong here?
Thank you
Alex
--------------------/Original Message ----------
Yes, you are creating multiple columns to hold data for each of the players.
Each player should go into a separate row.
You want something like:
CREATE TABLE pref_results (
Game_id varchar,
Player_id varchar,
Player_winnings numeric,
Player_position integer -- not truly required but useful for
generating columns later
);
CREATE TABLE pref_games (
Game_id varchar,
Game_rounds integer,
Game_finished_ts timestamptz
);
It is almost always wrong to have columns where you are simply adding a
sequential integer to the same base name.
However, to answer your question, you would need to JOIN the "pref_users"
table to the "pref_results" table THREE TIMES, once for each of (id0, id1,
id2).
SELECT *
FROM pref_results
JOIN pref_users user_0 ON (id0 = user_0.id)
JOIN perf_users user_1 ON (id1 = user_1.id)
JOIN perf_users user_2 ON (id1 = user_2.id)
Note the aliases for the pref_users table, and you would want to alias any
columns you end up pulling into the SELECT list.
Then you hope you never need to add a 4th player.
If you still want to present the data using 3 sets of columns for the
players you would need to perform a limited self-join:
SELECT
Game_id,
p1.Player_id AS P1_ID,
p2.Player_id AS P2_ID,
p3.Player_id AS P3_ID
FROM (SELECT ... FROM pref_results WHERE Player_position = 1) p1 USING
(Game_id)
JOIN (SELECT .. FROM pref_results WHERE Player_position = 2) p2 USING
(Game_id)
JOIN (SELECT .. FROM pref_results WHERE Player_position = 3) p2 USING
(Game_id)
Then add whatever columns and JOIN you need to get all the desired fields
into the output.
In this way you have a database model that is easy to query and insert data
into while still having the ability to view the data in a natural way
(horizontally). Add should you want to track a game with four players you
can still use the same data model and simply add a VIEW similar to the
three-person view but with a fourth set of columns for the fourth player.
David J.
On Oct 25, 2011, at 15:32, Alexander Farber wrote:
> Hello,
>
> I'm trying to save results of card game with 3 players into a table.
>
> It is bad enough, that I had to introduce
> 3 columns for user ids: id0, id1, id2 and
> 3 columns for their scores: money0, money1, money2 -
>
> create table pref_results (
> id0 varchar(32) references pref_users,
> id1 varchar(32) references pref_users,
> id2 varchar(32) references pref_users,
> money0 integer not null,
> money1 integer not null,
> money2 integer not null,
> rounds integer not null,
> finished timestamp default current_timestamp
> );
>
> But now I've also realized, that I don't know,
> how to join that table with the pref_users,
> so that I get first_name for each of 3 players -
>
> $sth = $db->prepare("
> select
> id0,
> id1,
> id2,
> money0,
> money1,
> money2,
> rounds,
> to_char(finished,'DD.MM.YYYY') as day
> from pref_results
> where finished > now() - interval '1 week'
> and (id0=? or id1=? or id2=?)
> ");
> $sth->execute(array($id, $id, $id));
>
> while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
> # XXX print the table with day, first_names and money
> }
>
> I'm probably doing something wrong here?
Likely. Are you only ever going to have three players per table? Seems unlikely.
Without knowing anything else about your application, I suspect you need more tables:
a games table
a games_players table with each row associating a single player with a game. A three-player game has three rows per
game.A four-player game would have four.
Depending on your app, you might also have finished_games and game_player_results tables.
Michael Glaesemann
grzm seespotcode net
On 26/10/11 08:32, Alexander Farber wrote:
> Hello,
>
> I'm trying to save results of card game with 3 players into a table.
>
> It is bad enough, that I had to introduce
> 3 columns for user ids: id0, id1, id2 and
> 3 columns for their scores: money0, money1, money2 -
>
> create table pref_results (
> id0 varchar(32) references pref_users,
> id1 varchar(32) references pref_users,
> id2 varchar(32) references pref_users,
> money0 integer not null,
> money1 integer not null,
> money2 integer not null,
> rounds integer not null,
> finished timestamp default current_timestamp
> );
>
> But now I've also realized, that I don't know,
> how to join that table with the pref_users,
> so that I get first_name for each of 3 players -
>
> $sth = $db->prepare("
> select
> id0,
> id1,
> id2,
> money0,
> money1,
> money2,
> rounds,
> to_char(finished,'DD.MM.YYYY') as day
> from pref_results
> where finished> now() - interval '1 week'
> and (id0=? or id1=? or id2=?)
> ");
> $sth->execute(array($id, $id, $id));
>
> while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
> # XXX print the table with day, first_names and money
> }
>
> I'm probably doing something wrong here?
>
> Thank you
> Alex
>
You may want to cosider:
finished > CURRENT_DATE - interval '1 week'
On 26/10/11 08:32, Alexander Farber wrote:
> Hello,
>
> I'm trying to save results of card game with 3 players into a table.
>
> It is bad enough, that I had to introduce
> 3 columns for user ids: id0, id1, id2 and
> 3 columns for their scores: money0, money1, money2 -
>
> create table pref_results (
> id0 varchar(32) references pref_users,
> id1 varchar(32) references pref_users,
> id2 varchar(32) references pref_users,
> money0 integer not null,
> money1 integer not null,
> money2 integer not null,
> rounds integer not null,
> finished timestamp default current_timestamp
> );
>
> But now I've also realized, that I don't know,
> how to join that table with the pref_users,
> so that I get first_name for each of 3 players -
>
> $sth = $db->prepare("
> select
> id0,
> id1,
> id2,
> money0,
> money1,
> money2,
> rounds,
> to_char(finished,'DD.MM.YYYY') as day
> from pref_results
> where finished> now() - interval '1 week'
> and (id0=? or id1=? or id2=?)
> ");
> $sth->execute(array($id, $id, $id));
>
> while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
> # XXX print the table with day, first_names and money
> }
>
> I'm probably doing something wrong here?
>
> Thank you
> Alex
>
Hi,
I agree with the othet replies that you should have the results in
separate tables, but I have tested out something similar to what you
want below.
First, couple of points to note:
(1) the interval logic you have, selects finish times within one week
of the current date and time. So if you run it at 11 am, then you miss
records at 10 am 7 days ago, but pick up records that finish at 11:30am
on that day!
(2) I changed the format of the date since numerically there is
sometimes ambiguity between dd.mm.yyyy and mm.dd.yyyy as American use
the latter (9/11 is November 9th to me, but to an American it is
September 11th)
(3) I have used an explicity money type
(4) I suggest that date/times should be stored in the database in GMT,
so that the dates can be dislayed appropriately in any l,ocale, hence
the use of 'timstamptz' (timestamp with timezone).
CREATE TABLE player
(
id int PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
UNIQUE (first_name, last_name)
);
CREATE TABLE pref_results
(
id int PRIMARY KEY,
rounds int NOT NULL,
finished timestamptz NOT NULL,
player0_id int NOT NULL REFERENCES player(id),
money0 money NOT NULL,
player1_id int NOT NULL REFERENCES player(id),
money1 money NOT NULL,
player2_id int NOT NULL REFERENCES player(id),
money2 money NOT NULL,
CONSTRAINT player0_player1_same CHECK (player0_id != player1_id),
CONSTRAINT player1_player2_same CHECK (player1_id != player2_id),
CONSTRAINT player2_player0_same CHECK (player2_id != player0_id)
);
SELECT
(SELECT first_name FROM player WHERE player.id = pr.player0_id) AS
player0,
(SELECT first_name FROM player WHERE player.id = pr.player1_id) AS
player1,
(SELECT first_name FROM player WHERE player.id = pr.player2_id) AS
player2,
pr.money0,
pr.money1,
pr.money2,
pr.rounds,
pr.finished,
to_char(pr.finished,'DD-MON-YYYY') as day
FROM
pref_results pr
WHERE
pr.finished > now() - interval '1 week'
ORDER BY
pr.finished,
pr.rounds;
Cheers,
Gavin
Hello again,
thank you for your replies. If I create a separate table for games:
create table pref_users (
uid varchar(32) primary key,
first_name varchar(64),
female boolean,
avatar varchar(128)
}
create table pref_games {
gid serial,
rounds integer not null,
finished timestamp default current_timestamp
}
create table pref_scores (
uid varchar(32) references pref_users,
gid serial references pref_games, /* XXX serial ok here? */
money integer not null,
quit boolean
);
then how do I find the new game id after I've just created it here:
create or replace function pref_insert_scores(
_uid0 varchar, _money0 integer, _quit0 boolean,
_uid1 varchar, _money1 integer, _quit1 boolean,
_uid2 varchar, _money2 integer, _quit2 boolean,
_rounds integer) returns void as $BODY$
begin
insert into pref_games (rounds) values (_rounds);
-- XXX how do I get the _gid of this new game?
insert into pref_scores (uid, gid, money, quit)
values(_uid0, _gid, _money0, _quit0);
insert into pref_scores (uid, gid, money, quit)
values(_uid1, _gid, _money1, _quit1);
insert into pref_scores (uid, gid, money, quit)
values(_uid2, _gid, _money2, _quit2);
end;
$BODY$ language plpgsql;
Thank you! I've listed few more details at
http://stackoverflow.com/questions/7899995/save-scores-of-3-players-per-game-into-postgresql
Regards
Alex
On 26 October 2011 10:08, Alexander Farber <alexander.farber@gmail.com> wrote:
> create table pref_games {
> gid serial,
> rounds integer not null,
> finished timestamp default current_timestamp
> }
> then how do I find the new game id after I've just created it here:
>
> create or replace function pref_insert_scores(
> _uid0 varchar, _money0 integer, _quit0 boolean,
> _uid1 varchar, _money1 integer, _quit1 boolean,
> _uid2 varchar, _money2 integer, _quit2 boolean,
> _rounds integer) returns void as $BODY$
> begin
>
> insert into pref_games (rounds) values (_rounds);
>
> -- XXX how do I get the _gid of this new game?
Use insert .. returning gid with a cursor:
declare
game_cur cursor (n integer) for insert into pref_games (rounds)
values (n) returning gid;
_rec record;
begin
for _rec in game_cur(_rounds) loop
insert into pref_scores (uid, _rec.gid, money, quit) values...
end loop;
> insert into pref_scores (uid, gid, money, quit)
> values(_uid0, _gid, _money0, _quit0);
>
> insert into pref_scores (uid, gid, money, quit)
> values(_uid1, _gid, _money1, _quit1);
>
> insert into pref_scores (uid, gid, money, quit)
> values(_uid2, _gid, _money2, _quit2);
> end;
> $BODY$ language plpgsql;
>
> Thank you! I've listed few more details at
> http://stackoverflow.com/questions/7899995/save-scores-of-3-players-per-game-into-postgresql
>
> 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
>
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
Hello again,
still I can't figure out how to perform a join
to fetch all games where a player has participated -
I have a table containing all games played:
# select * from pref_games limit 5;
gid | rounds | finished
-----+--------+----------------------------
1 | 10 | 2011-10-26 14:10:35.46725
2 | 12 | 2011-10-26 14:34:13.440868
3 | 12 | 2011-10-26 14:34:39.279883
4 | 14 | 2011-10-26 14:35:25.895376
5 | 14 | 2011-10-26 14:36:56.765978
And I have a table with scores of each of 3 players:
# select * from pref_scores where gid=3;
id | gid | money | quit
-----------------------+-----+-------+------
OK515337846127 | 3 | -37 | f
OK40798070412 | 3 | -75 | f
MR2871175175044094219 | 3 | 112 | f
(Which means 3 players have played game #3
and 1 has won 112, while 2 have lost 37 + 75)
My problem is: I'd like to list all games played
by 1 player, with all participants and scores listed.
I'm trying (these are all games played by DE9411):
# select * from pref_scores where id='DE9411';
id | gid | money | quit
--------+-----+-------+------
DE9411 | 43 | 64 | f
DE9411 | 159 | -110 | f
DE9411 | 224 | 66 | f
DE9411 | 297 | -36 | f
DE9411 | 385 | 29 | f
DE9411 | 479 | -40 | f
DE9411 | 631 | -14 | f
DE9411 | 699 | 352 | f
DE9411 | 784 | -15 | f
DE9411 | 835 | 242 | f
Then I'm trying to join with pref_games:
# select s.id, s.gid, s.money, s.quit, to_char(g.finished,
'DD.MM.YYYY') as day from pref_scores s, pref_games g where
s.gid=g.gid and s.id='DE9411';
id | gid | money | quit | day
--------+-----+-------+------+------------
DE9411 | 43 | 64 | f | 26.10.2011
DE9411 | 159 | -110 | f | 26.10.2011
DE9411 | 224 | 66 | f | 26.10.2011
DE9411 | 297 | -36 | f | 26.10.2011
DE9411 | 385 | 29 | f | 26.10.2011
DE9411 | 479 | -40 | f | 26.10.2011
DE9411 | 631 | -14 | f | 26.10.2011
DE9411 | 699 | 352 | f | 26.10.2011
DE9411 | 784 | -15 | f | 26.10.2011
DE9411 | 835 | 242 | f | 26.10.2011
But how do I display the 2 other players and
their scores in the above result set?
(More info on my problem:
http://stackoverflow.com/questions/7899995/save-scores-of-3-players-per-game-into-postgresql
)
Thank you
Alex
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Wednesday, October 26, 2011 4:16 PM
Cc: pgsql-general
Subject: Re: [GENERAL] Saving score of 3 players into a table
Hello again,
still I can't figure out how to perform a join to fetch all games where a
player has participated - I have a table containing all games played:
# select * from pref_games limit 5;
gid | rounds | finished
-----+--------+----------------------------
1 | 10 | 2011-10-26 14:10:35.46725
2 | 12 | 2011-10-26 14:34:13.440868
3 | 12 | 2011-10-26 14:34:39.279883
4 | 14 | 2011-10-26 14:35:25.895376
5 | 14 | 2011-10-26 14:36:56.765978
And I have a table with scores of each of 3 players:
# select * from pref_scores where gid=3;
id | gid | money | quit
-----------------------+-----+-------+------
OK515337846127 | 3 | -37 | f
OK40798070412 | 3 | -75 | f
MR2871175175044094219 | 3 | 112 | f
(Which means 3 players have played game #3 and 1 has won 112, while 2 have
lost 37 + 75)
My problem is: I'd like to list all games played by 1 player, with all
participants and scores listed.
I'm trying (these are all games played by DE9411):
# select * from pref_scores where id='DE9411';
id | gid | money | quit
--------+-----+-------+------
DE9411 | 43 | 64 | f
DE9411 | 159 | -110 | f
DE9411 | 224 | 66 | f
DE9411 | 297 | -36 | f
DE9411 | 385 | 29 | f
DE9411 | 479 | -40 | f
DE9411 | 631 | -14 | f
DE9411 | 699 | 352 | f
DE9411 | 784 | -15 | f
DE9411 | 835 | 242 | f
Then I'm trying to join with pref_games:
# select s.id, s.gid, s.money, s.quit, to_char(g.finished,
'DD.MM.YYYY') as day from pref_scores s, pref_games g where s.gid=g.gid and
s.id='DE9411';
id | gid | money | quit | day
--------+-----+-------+------+------------
DE9411 | 43 | 64 | f | 26.10.2011
DE9411 | 159 | -110 | f | 26.10.2011
DE9411 | 224 | 66 | f | 26.10.2011
DE9411 | 297 | -36 | f | 26.10.2011
DE9411 | 385 | 29 | f | 26.10.2011
DE9411 | 479 | -40 | f | 26.10.2011
DE9411 | 631 | -14 | f | 26.10.2011
DE9411 | 699 | 352 | f | 26.10.2011
DE9411 | 784 | -15 | f | 26.10.2011
DE9411 | 835 | 242 | f | 26.10.2011
But how do I display the 2 other players and their scores in the above
result set?
(More info on my problem:
http://stackoverflow.com/questions/7899995/save-scores-of-3-players-per-game
-into-postgresql
)
Thank you
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 ---------------
SELECT *
FROM pref_scores
WHERE gid IN ( SELECT gid FROM pref_games WHERE id = 'DE9411' );
-- Note, "EXISTS" may be faster for large datasets but "IN", at least to me,
is much easier to understand.
In this case you want all scores for games in which person 'DE9411' was a
player.
David J.
On Oct 26, 2011, at 16:15, Alexander Farber wrote:
> Hello again,
>
> still I can't figure out how to perform a join
> to fetch all games where a player has participated -
> I have a table containing all games played:
>
> # select * from pref_games limit 5;
> gid | rounds | finished
> -----+--------+----------------------------
> 1 | 10 | 2011-10-26 14:10:35.46725
> 2 | 12 | 2011-10-26 14:34:13.440868
> 3 | 12 | 2011-10-26 14:34:39.279883
> 4 | 14 | 2011-10-26 14:35:25.895376
> 5 | 14 | 2011-10-26 14:36:56.765978
>
> And I have a table with scores of each of 3 players:
>
> # select * from pref_scores where gid=3;
> id | gid | money | quit
> -----------------------+-----+-------+------
> OK515337846127 | 3 | -37 | f
> OK40798070412 | 3 | -75 | f
> MR2871175175044094219 | 3 | 112 | f
>
> (Which means 3 players have played game #3
> and 1 has won 112, while 2 have lost 37 + 75)
>
> My problem is: I'd like to list all games played
> by 1 player, with all participants and scores listed.
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;
Michael Glaesemann
grzm seespotcode net
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
-----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.
The PostgreSQL docs are unfortunately scarce on JOINs http://www.postgresql.org/docs/8.4/static/tutorial-join.html I've never seen a JOIN producing several rows instead of columns before Michael suggested it in this thread
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@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:
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
Fair enough. But look in the SQL Commands section under SELECT (FROM clause) as well, as that gives you the syntax and meaningand not just an overview of the concept. David J. On Oct 27, 2011, at 8:27, Alexander Farber <alexander.farber@gmail.com> wrote: > The PostgreSQL docs are unfortunately scarce on JOINs > http://www.postgresql.org/docs/8.4/static/tutorial-join.html > > I've never seen a JOIN producing several rows instead > of columns before Michael suggested it in this thread > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general