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 INTEGER NOT 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 DEFAULT 0,
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 | 9
paul | 2 | 6
marcyun | 1 | 2
paulie | 1 | 2
winstonchurchill | 1 | 2
djk121 | 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
