Get team names with a subquery
Let's solve a problem we've encountered a few times in this course so far -- How do you get both the home and away team names into one final query result?
Out of the 4 techniques we just discussed, this can be performed using subqueries, correlated subqueries, and CTEs. Let's practice creating similar result sets using each of these 3 methods over the next 3 exercises, starting with subqueries in FROM
.
Warning: if your code times out, you probably made a mistake in the JOIN
and tried to join on the wrong fields, which caused the table to be too big! Read the provided code and comments carefully, and check your ON
conditions!
This exercise is part of the course
Data Manipulation in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
m.id,
t.team_long_name AS hometeam
-- Left join team to match
FROM ___ AS m
___ ___ ___ as t
ON m.hometeam_id = team_api_id;