How can I derive the winners of a match from a table of games that relate to a table of matches?

Question

In a database that stores tournaments I have this relational structure to store matches and their games:

EER Diagram

When a match is created, a match row and multiple game rows are inserted corresponding to the number of games in the match. Match_left and match_right players rows are also inserted then to relate the two players in a match to the match row. When a player is recorded as having won a game, a row related to the corresponding game is inserted into the game_winners table. Is there a way to write a query to derive who won matches from won games? Perhaps logically this should be a view so that I can join match table rows to it at any time easily?

Here is some sample data

I would expect Fred to be returned as the winner of match_ID 1 because he won 2/3 games. I would expect the winner of match_ID 2 to be null because neither player has won the necessary number of games to win (2/3).

Answer 1

I'm assuming here that the winner of a match is the player who's won the most games.

Ranking the players within a match is straightforward.

SELECT player_id, COUNT(*) AS game_count
  FROM game_winners
  WHERE match_id = ?
  GROUP BY player_id
  ORDER BY COUNT(*) DESC

Finding the winners of more than one match at once is a little tricker.

SELECT match_id, player_id, COUNT(*) AS game_count
  FROM game_winners
  GROUP BY match_id, player_id

Let's call the above player_wins

SELECT match_id, MAX(game_count) AS match_winner
  FROM player_wins
  GROUP BY match_id

This gives you the winning score for each match.

SELECT match_id, player_id
  FROM player_wins
  INNER JOIN (
    SEELCT match_id, MAX(game_count) AS match_winner
    FROM player_wins
    GROUP BY match_id
  ) AS winning_score
  ON player_wins.match_id = winning_score.match_id
  AND player_wins.game_count = winning_score.match_winner

This gives you the winning player for each match.

Answer 2

Let's see if I've understood your sample data correctly.

SELECT match_id, round_id, COUNT(*) AS game_count
  FROM games
  GROUP BY match_id, round_id

This gives you the number of games in each match. The match is a best of format, so you have to win more than half of the number of games to win the match.

SELECT match_id, round_id, player_id, COUNT(*) AS win_count
  FROM game_winners
  GROUP BY match_id, round_id

This gives you the number of games won by each player in each match.

SELECT match_id, round_id, player_id
  FROM game_winners
  GROUP BY match_id, round_id
  HAVING COUNT(*) * 2 > (
    SELECT COUNT(*)
      FROM games
      WHERE games.match_id = game_winners.match_id
      AND games.round_id = game_winners.round_id
  )

So one option is to add a check that the player has won more than half the number of games in the match via a subquery.

SELECT games_won.match_id, games_won.round_id, games_won.player_id
  FROM (
    SELECT match_id, round_id, player_id, COUNT(*) AS win_count
      FROM game_winners
      GROUP BY match_id, round_id
    ) AS games_won
  INNER JOIN (
    SELECT match_id, round_id, COUNT(*) AS game_count
      FROM games
      GROUP BY match_id, round_id
    ) AS all_games
  ON games_won.match_id = game_count.match_id
  AND games_won.round_id = game_count.round_id
  WHERE games_won.win_count * 2 > all_games.game_count

Or if you prefer you can do it using a join.