You are complicating things: this should be a 2-n not n-m relationship.
Get rid of teams_games
table and make 2 fields to games table, ex: home_tid
and away_tid
EDIT: and the query would be then something similar:
select t.tid, t.name,
sum(g.home_score < g.away_score xor t.tid = g.home_tid) wins,
sum(g.home_score > g.away_score xor t.tid = g.home_tid) losses,
sum(g.home_score = g.away_score) draws
from games g
join teams t on t.tid = g.home_tid or t.tid = g.away_tid
group by t.tid
so the answer is to use sum, otherwise it counts both true and false values in output which is total rows resulting from joins.
EDIT2:
select t.tid, t.name,
sum(g.home_score < g.away_score xor tg.homeoraway) wins,
sum(g.home_score > g.away_score xor tg.homeoraway) losses,
sum(g.home_score = g.away_score) draws
from games g
join team_games tg on tg.gid = g.gid
join teams t on t.tid = tg.tid
group by t.tid
Imre L
2010-06-29 20:35:11