Full-Time Results Board for the Football League
The league's website shows a results board. Each fixture has a home and an away side; a fixture that was postponed has NULL in both goal columns.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #450
Problem statement
The league's website shows a results board. Each fixture has a home and an away side; a fixture that was postponed has NULL in both goal columns.
Return every fixture with the columns match_id, home_team, away_team, result and winner. result is 'Home win', 'Away win', 'Draw' or, for a postponed fixture, 'Postponed'. winner is the winning team's name, and NULL for a draw or a postponed fixture. Order the rows by match_date, then by match_id.
Tables
Table: Fixture
| Column | Type |
|---|---|
| match_id | int |
| match_date | date |
| home_team | varchar |
| away_team | varchar |
| home_goals | int |
| away_goals | int |
Primary key: match_id.
home_goals and away_goals are both NULL when the fixture was postponed, and both set otherwise.
Examples
Example 1
Fixture
| match_id | match_date | home_team | away_team | home_goals | away_goals |
|---|---|---|---|---|---|
| 1 | 2024-09-14 | Goa Gulls | Pune Rovers | 2 | 1 |
| 2 | 2024-09-14 | Delhi Falcons | Shillong Stars | 0 | 0 |
| 3 | 2024-09-15 | Chennai Strikers | Mumbai Mariners | 1 | 3 |
| 4 | 2024-09-21 | Pune Rovers | Delhi Falcons | NULL | NULL |
| 5 | 2024-09-21 | Mumbai Mariners | Goa Gulls | 2 | 2 |
| 6 | 2024-09-22 | Shillong Stars | Chennai Strikers | 4 | 0 |
Output
| match_id | home_team | away_team | result | winner |
|---|---|---|---|---|
| 1 | Goa Gulls | Pune Rovers | Home win | Goa Gulls |
| 2 | Delhi Falcons | Shillong Stars | Draw | NULL |
| 3 | Chennai Strikers | Mumbai Mariners | Away win | Mumbai Mariners |
| 4 | Pune Rovers | Delhi Falcons | Postponed | NULL |
| 5 | Mumbai Mariners | Goa Gulls | Draw | NULL |
| 6 | Shillong Stars | Chennai Strikers | Home win | Shillong Stars |
How to solve Full-Time Results Board for the Football League
Each output row is one fixture, so there is no grouping — just two CASE expressions. The first names the result. Its branches are checked in order, and the order matters: a postponed fixture has NULL goals, and NULL > NULL and NULL < NULL are both unknown, so without a first branch for home_goals IS NULL a postponed match would fall through to ELSE 'Draw'. Putting the NULL test first makes the remaining branches only ever see played matches.
The second CASE returns the home team when it scored more and the away team when it scored less. A draw matches neither branch, and neither does a postponed fixture (the comparisons are unknown), and a CASE with no matching branch and no ELSE yields NULL — exactly the rule asked for. Nested IF calls say the same thing.
Order by date, then by match_id so fixtures on the same day are listed predictably. The query is a single scan.
Reference solution (MySQL)
SELECT match_id, home_team, away_team,
CASE
WHEN home_goals IS NULL THEN 'Postponed'
WHEN home_goals > away_goals THEN 'Home win'
WHEN home_goals < away_goals THEN 'Away win'
ELSE 'Draw'
END AS result,
CASE
WHEN home_goals > away_goals THEN home_team
WHEN home_goals < away_goals THEN away_team
END AS winner
FROM Fixture
ORDER BY match_date, match_idAnother way
SELECT match_id, home_team, away_team, IF(home_goals IS NULL, 'Postponed', IF(home_goals > away_goals, 'Home win', IF(home_goals < away_goals, 'Away win', 'Draw'))) AS result, IF(home_goals > away_goals, home_team, IF(home_goals < away_goals, away_team, NULL)) AS winner FROM Fixture ORDER BY match_date, match_id← Economical Bowlers of the T20 Season · Fantasy Teams That Forgot to Pick a Captain →