fumbling for join syntax

From: Daniel Kelley <dkelley(at)otec(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: fumbling for join syntax
Date: 2002-09-05 14:29:55
Message-ID: Pine.BSF.4.20L2.0209051002120.7508-100000@mx1.gc.ny.otec.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2002-09-05 15:55:54 Re: fumbling for join syntax
Previous Message Bruce Momjian 2002-09-05 05:27:22 Re: LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?