Setting up the away team CTE

Great job! Now that you have a query identifying the home team in a match, you will perform a similar set of steps to identify the away team. Just like the previous step, you will join the match and team tables. Each of these two queries will be declared as a Common Table Expression in the following step.

The primary difference in this query is that you will be joining the tables on awayteam_id, and reversing the match outcomes in the CASE statement.

When altering CASE statement logic in your own work, you can reverse either the logical condition (i.e., home_goal > away_goal) or the outcome in THEN -- just make sure you only reverse one of the two!

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Complete the CASE statement syntax.
Fill out the logical operators identifying each match as a win, loss, or tie for Manchester United.
Join the table on awayteam_id, and team_api_id.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT 
	m.id, 
    t.team_long_name,
    -- Identify matches as home/away wins or ties
	___ ___ m.home_goal ___ ___ 'MU Loss'
		___ m.home_goal ___ ___ 'MU Win'
        ___ 'Tie' ____ AS outcome
-- Join team table to the match table
FROM ___ AS m
LEFT JOIN ___ AS t 
ON ___ = t.team_api_id
WHERE 
	-- Filter for 2014/2015 and Manchester United as the away team
	___ = ___
	AND t.team_long_name = ___;

Edit and Run Code