I have a query I'm having trouble with that involves 3 tables described below. There are more fields that are immaterial to this query.
users: id display_name matches: id league_seasons_id date_played winner_id loser_id players: id match_id player_id nth_match division result # values are "Win" or "Loss" end_hcp
I'm trying to produce output like this:
Name Hcp Wins Losses High Run HR Pct of Hcp Alan 705 12 2 27 3.83% Bill 815 10 4 30 3.68% Charlie 611 9 5 12 1.96% Denise 788 8 6 15 1.90% ...
The query below mostly works, except for the
Hcp column. In the players table there are 2 records for each match, one with the winner's info and one with the loser's. In each record is the
start_hcp and the
end_hcp, i.e. the handicap that player had before playing the match and the new one based on the match.
I need the
end_hcp from the very last match that each player played up until now (the date/time of the query). I'm guessing there's going to be a subquery involved but I haven't figured out what it is.
SELECT p.division AS Division, u.display_name AS Name, p.end_hcp AS Handicap, count(if(result = "Win", 1, NULL)) AS Wins, count(if(result = "Loss", 1, NULL)) AS Losses, max(high_run) AS "High Run", concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp" FROM `lwljhb_lwl_players` AS p, `lwljhb_lwl_matches` AS m, `lwljhb_users` AS u WHERE m.league_seasons_id = 9 AND p.match_id = m.id AND p.player_id = u.id AND u.display_name <> "Bye" GROUP BY u.display_name ORDER BY p.division, Wins DESC, Losses