FIRST Ranking Software

[Nibbles]

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.

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