Re: fumbling for join syntax

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