COUNT number of wins and MAX participant with the most wins with MySQL

I have a table called games with the fields: match and winner.

• Match is a foreign key to the matches table;
• winner is a foreign key to the 'participants' table that can be NULL if the g开发者_开发问答ame is still undecided.

I need a way to find out who won each match when I select a list of matches by counting who won each game and returning the participant (from the winner field) who had the most wins.

Various combinations of subqueries, GROUP BY, COUNT, and MAX have yet to give me the right results.

I don't think I can substitute ORDER BY and LIMIT for MAX because that would return just the first participant in a match if all participants happened to have the same number of wins.

Have you tried using JOINs with GROUP BY and COUNT ?

select b.name, count(c.match)
from game a
inner join part b on a.winner = b.winner
inner join matches c on a.match = c.match
group by b.winner;

John