Exercise

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 will 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.

Instructions

100 XP
  • Declare the home and away 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 from match in the main query.