Complicated football league Dynamic Ordering in MySQL?
- by Dan
I have a table 'games' for a football league as follows:
date home_team_id away_team_id home_score away_score
- 1 2 6 21
- 3 1 7 19
I can't figure out how to dynamically generate a list of team ID's ordered by Wins (then points for if poss)?
--
I have this query which works fine when I have a $team_id but of cause then I can only do 1 team at a time, and that doesn't allow for ordering at query level
((SELECT COUNT(*) FROM `games` WHERE ((`home_score` > `away_score`) AND `home_team_id` = '.$team_id.')) +
(SELECT COUNT(*) FROM `games` WHERE ((`home_score` < `away_score`) AND `away_team_id` = '.$team_id.'))) AS `wins`
I wonder if i can use this with some form of GROUP, or mySQL can know the $team_id itself?
I've also tried some multiple JOINs with the 'team' table but they didn't work either.
Thanks,
Dan