Lesson 12.4

SQL JOIN Practice: Linking Customers, Rentals, and Movies

Stitch three tables together so a rental reads as a name and a title, not a pair of ids.

The rentals table records a customer_id and a movie_id, but not the customer's name or the film's title. Those live in the customers and movies tables, reached by their keys.

A single query can join more than two tables. Start from the table in the middle: rentals, and add one JOIN for each related table you need.

Build the joins before you filter

Get the relationships working first, then add the WHERE clause. Because the join brings customer columns into the same result, you can filter on c.city even though the city never appears in the rentals table. Table aliases like r, c, and m keep the column references short and unambiguous.

Pattern

select a.detail, b.detail, c.detail
from fact_table as a
join dim_one as b on a.b_id = b.b_id
join dim_two as c on a.c_id = c.c_id
where b.filter_column = 'value'

Schema · Movie Rentals Tables · rentals, customers, movies 3 tables

Table · rentals

One row per rental. Movies still out have a null returned_date.

6 columns · 26 rows

rental_id int customer_id int movie_id int rental_date date returned_date date amount decimal

Table · customers

One row per customer.

5 columns · 10 rows

customer_id int first_name text last_name text city text joined_date date

Table · movies

One row per movie in the catalog.

5 columns · 12 rows

movie_id int title text genre text release_year int rating decimal

Your task

For rentals by Portland customers, return the customer's first name, last name, the movie title, the rental date, and the amount. Order by rental date, oldest first.