Putting the CTEs together
Now that you've created the two subqueries identifying the home and away team opponents, it's time to rearrange your query with the home
and away
subqueries as Common Table Expressions (CTEs). You'll notice that the main query includes the phrase, SELECT DISTINCT
. Without identifying only DISTINCT
matches, you might return a duplicate record for each game played.
Continue building the query to extract all matches played by Manchester United in the 2014/2015 season.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Declare the
home
andaway
CTEs before your main query. - Join your CTEs to the match table using a
LEFT JOIN
. - Select the relevant data from the CTEs into the main query.
- Select the date from
match
, team names from the CTEs, and home/ away goals frommatch
in the main query.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Set up the home team CTE
___ ___ ___ (
SELECT m.id, t.team_long_name,
CASE WHEN m.home_goal > m.away_goal THEN 'MU Win'
WHEN m.home_goal < m.away_goal THEN 'MU Loss'
ELSE 'Tie' END AS outcome
FROM match AS m
LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),
-- Set up the away team CTE
___ ___ (
SELECT m.id, t.team_long_name,
CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss'
WHEN m.home_goal < m.away_goal THEN 'MU Win'
ELSE 'Tie' END AS outcome
FROM match AS m
LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)
-- Select team names, the date and goals
SELECT DISTINCT
___,
___.___ AS home_team,
___.___ AS away_team,
___,
m.away_goal
-- Join the CTEs onto the match table
FROM match AS m
___ JOIN ___ ON m.id = ___
___ JOIN ___ ON m.id = ___
WHERE m.season = '2014/2015'
AND (home.team_long_name = 'Manchester United'
OR away.team_long_name = 'Manchester United');