[Jaci]

Originally Posted by markmcgary

Thank you for this. Really great work. Your 'more advanced' example intrigued me because I know our team was a 1st pick for alliance 5 at the OCR (CAPL) and we had a higher OPR. When I ran your query, we did not appear in the results. I did a little testing and found an improvement to the SQL.

SELECT events.short_name, alliances.num, teams.id, stats.opr FROM alliances
INNER JOIN alliance_picks ON alliances.id == alliance_picks.alliance
INNER JOIN events ON events.id == alliances.event
INNER JOIN teams ON alliance_picks.team == teams.id 
INNER JOIN stats ON stats.team == teams.id
WHERE alliance_picks.pick == 0 
AND alliances.event == stats.event
AND
(SELECT stats.opr FROM alliance_picks
INNER JOIN stats ON stats.team == alliance_picks.team
WHERE alliance_picks.alliance == alliances.id
AND stats.event = alliances.event
AND alliance_picks.pick == 1)
> stats.opr
ORDER BY events.short_name, alliances.num

Now, we appear in the results as expected!

Thanks again for putting this together.

Ahh, keen eye, good job! I can't wait to see what queries ya'll can come up with using this Data Dump