Full-Time Results Board for the Football League

Easy
450.

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.

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

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar
home_goalsint
away_goalsint

Primary key: match_id.

home_goals and away_goals are both NULL when the fixture was postponed, and both set otherwise.

Example

The rows must come back in the order shown.

Fixture

match_idmatch_datehome_teamaway_teamhome_goalsaway_goals
12024-09-14Goa GullsPune Rovers21
22024-09-14Delhi FalconsShillong Stars00
32024-09-15Chennai StrikersMumbai Mariners13
42024-09-21Pune RoversDelhi FalconsNULLNULL
52024-09-21Mumbai MarinersGoa Gulls22
62024-09-22Shillong StarsChennai Strikers40

Output

match_idhome_teamaway_teamresultwinner
1Goa GullsPune RoversHome winGoa Gulls
2Delhi FalconsShillong StarsDrawNULL
3Chennai StrikersMumbai MarinersAway winMumbai Mariners
4Pune RoversDelhi FalconsPostponedNULL
5Mumbai MarinersGoa GullsDrawNULL
6Shillong StarsChennai StrikersHome winShillong Stars
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar
home_goalsint
away_goalsint

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_idmatch_datehome_teamaway_teamhome_goalsaway_goals
12024-09-14Goa GullsPune Rovers21
22024-09-14Delhi FalconsShillong Stars00
32024-09-15Chennai StrikersMumbai Mariners13
42024-09-21Pune RoversDelhi FalconsNULLNULL
52024-09-21Mumbai MarinersGoa Gulls22
62024-09-22Shillong StarsChennai Strikers40

Output

match_idhome_teamaway_teamresultwinner
1Goa GullsPune RoversHome winGoa Gulls
2Delhi FalconsShillong StarsDrawNULL
3Chennai StrikersMumbai MarinersAway winMumbai Mariners
4Pune RoversDelhi FalconsPostponedNULL
5Mumbai MarinersGoa GullsDrawNULL
6Shillong StarsChennai StrikersHome winShillong 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_id

Another 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 →