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