Five table SQL join. Fun. (Four is all you need, actually, and it would be six if I only knew $eventid but not $season) This database is structured such that a table can contain data for multiple seasons and events, $eventid and $season are substituted for the database-specific event ID and year, respectively. It what I use for
my scouting program to update team rankings.
The IF function is specific to MySQL, any other database will need to replace it with a standard IF/ELSE or CASE statment.
Code:
SELECT
t.teamnum,
SUM(IF(a.score>o.score,1,0)) AS won,
SUM(IF(a.score<o.score,1,0)) AS loss,
SUM(IF(a.score=o.score,1,0)) AS tie,
SUM(CASE
WHEN a.score>o.score THEN 2
WHEN a.score=o.score THEN 1
WHEN a.score<o.score THEN 0
END) AS qualify,
SUM(CASE
WHEN a.score>=o.score THEN a.points
WHEN a.score<o.score THEN o.score
END)/SUM(1) AS ranking,
SUM(1) AS matches
FROM
team t LEFT OUTER JOIN team_event u ON t.teamnum=u.teamnum,
team_match m, alliance a, alliance o
WHERE m.surrogate=0 AND m.disqualified=0
AND t.teamnum=m.teamnum
AND a.matchid=m.matchid
AND o.matchid=m.matchid
AND a.alliance=m.alliance
AND o.alliance!=m.alliance
AND t.season=$season
AND u.eventid=$eventid
AND m.eventid=u.eventid
AND a.eventid=u.eventid
AND o.eventid=u.eventid
GROUP BY t.teamnum