Saving score of 3 players into a table

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general