-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber Sent: Tuesday, October 25, 2011 3:33 PM To: pgsql-general Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
