fumbling for join syntax - Mailing list pgsql-sql

hi-

i have two tables which are used to track questions and answers in a
trivia game.  the structure of the tables is as follows:

CREATE TABLE triv_q_r (       id              VARCHAR(10) CONSTRAINT triv_q_r_pkey PRIMARY KEY,       t_id
VARCHAR(10)CONSTRAINT triv_q_r_t_id_fkey NOT NULL                                       REFERENCES triv_r(id)
                           ON DELETE CASCADE,       question        VARCHAR(1024) NOT NULL,       num
INTEGERNOT NULL,       c1              VARCHAR(1024) NOT NULL,       c2              VARCHAR(1024) NOT NULL,       c3
          VARCHAR(1024),       c4              VARCHAR(1024),       c5              VARCHAR(1024),       ans
INTEGER NOT NULL,       exp             VARCHAR(1024),       tm              INTEGER NOT NULL DEFAULT 1
 
);

CREATE TABLE triv_a_r (       tq_id           VARCHAR(10) CONSTRAINT triv_a_r_tq_id_fkey NOT
NULL                               REFERENCES triv_q_r(id)                               ON DELETE CASCADE,       fid_c
         VARCHAR(10) NOT NULL,       login           VARCHAR(20) NOT NULL,       ans             INTEGER NOT NULL
DEFAULT0,       stime           TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,       etime           TIMESTAMP(2),
  tm              INTEGER NOT NULL DEFAULT 0
 
);

right now, i'm givng a scoring overview using the following query:

select a.login as user, count(a.login) as score, sum(a.tm)/1000 as time
from triv_a_r a, triv_q_r b where
a.tq_id = b.id and a.ans = b.ans
group by a.login
order by score desc, time asc;

triv=> \i scoring.sql      user       | score | time
------------------+-------+------jobtester        |     3 |    9paul             |     2 |    6marcyun          |     1
|   2paulie           |     1 |    2winstonchurchill |     1 |    2djk121           |     1 |    3
 

this works fine, but it's been requested that the scoring query also
include scores for those who answered incorrectly.

i thought that i might be able to do this with a left outer join:

select triv_a_r.login as user, count(triv_a_r.login) as score,
sum(triv_a_r.tm)/1000 as time
from triv_a_r LEFT OUTER JOIN triv_q_r ON (triv_a_r.tq_id = triv_q_r.id)
where triv_a_r.ans = triv_q_r.ans
group by triv_a_r.login
order by score desc, time asc;

but this gives me exactly the same results as before, because i still need 

where triv_a_r.ans = triv_q_r.ans

to determine if a question was answered correctly.

in short, i'd like to have a single query that extracts the following from
these two tables:

number attempted, number correct, score, total time, login

any pointers woudl be greatly appreciated.

thanks-

dan