pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfumbling for join syntax
2+ messages / 2 participants
[nested] [flat]
* fumbling for join syntax
@ 2002-09-05 14:29 Daniel Kelley <dkelley@otec.com>
2002-09-05 15:55 ` Re: fumbling for join syntax Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Daniel Kelley @ 2002-09-05 14:29 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: fumbling for join syntax
2002-09-05 14:29 fumbling for join syntax Daniel Kelley <dkelley@otec.com>
@ 2002-09-05 15:55 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2002-09-05 15:55 UTC (permalink / raw)
To: Daniel Kelley <dkelley@otec.com>; pgsql-sql
Daniel,
> 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;
A little SQL trick:
select triv_a_r.login as user,
SUM(CASE WHEN triv_a_r.ans = triv_q_r.ans THEN 1 ELSE 0 END) 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)
group by triv_a_r.login
order by score desc, time asc;
Josh Berkus
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-05 15:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-05 14:29 fumbling for join syntax Daniel Kelley <dkelley@otec.com>
2002-09-05 15:55 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox