Premier League Predictor: Astro — Chapter 7, Exercise 1 ==================================================== TASK Explain, in your own words, why a single fixtures row can't be read directly as one team's own league-table result, and describe exactly what the UNION ALL inside team_fixture_stats does to fix that. SOLUTION A single row in fixtures represents one match, with home_team_id paired against home_score and away_team_id paired against away_score. That's a genuinely fine way to store one game, but it's not the same shape a league table needs: a league table wants a row per TEAM, showing that team's own goals-for and goals-against, regardless of which side of the match they happened to be on. The trouble is that where a given team's own goals-for actually lives in the row depends on whether that team was playing at home or away in that particular fixture — sometimes it's home_score, sometimes it's away_score. A query that only ever reads home_score as "goals for" would silently ignore every goal a team scored while playing away, and vice versa. The UNION ALL inside team_fixture_stats fixes this by turning every single played fixture row into two separate rows instead of one, each already written in the same neutral team_id/goals_for/goals_against shape: - The first half of the UNION ALL reads the fixture from the home team's own perspective: team_id = home_team_id, goals_for = home_score, goals_against = away_score. - The second half reads the exact same fixture from the away team's own perspective: team_id = away_team_id, goals_for = away_score, goals_against = home_score. UNION ALL (rather than a plain UNION) keeps both rows even though they come from the same fixture and are structurally similar, since there's no reason to deduplicate them — they represent two genuinely different teams' own results, not two copies of the same fact. Once both halves exist, every row in team_fixture_stats already answers "how did this one team do in this one fixture" in an identical shape, so aggregating them per team afterward is just ordinary GROUP BY arithmetic with no special-casing for home vs. away needed anywhere past this point. WHY THIS WORKS AS AN ANSWER ---------------------------- It correctly identifies the real underlying problem (a fixture's own column layout depends on which side a team was on, so "goals for" isn't a single fixed column to read from), and walks through exactly what each half of the UNION ALL produces and why UNION ALL rather than UNION is the correct choice, rather than only asserting that "UNION ALL combines the two queries."