In this tutorial, we will expand on what we’ve learned about JOIN and combine multiple tables with filtering and viewing useful information.

#Table of Contents

Query 1. Check the overdue fees owed by customers and the number of times they were late returning movies by combining 5 different tables.

Let’s review JOIN first. Do you remember what this function does?

JOIN allows you to combine two or more tables using a common column (Join Key).

Different kinds of JOIN:

You can use different  JOIN  functions depending on the information you want to see. In this next example, we will use INNER JOINto combine two tables and only view rentals that are past due.

Q1. Check the overdue fees owed by customers and the number of times they were late returning movies by combining 5 different tables.

Let's say that we want to give the customers the least amount of late fees a coupon in our imaginary DVD store. To find who they are, we have to combine multiple tables that contain different levels of data. We specified the columns we will use to connect all five tables below:

To calculate an accurate fee for late returns, we need to know how long the movie was rented and how much it costs to rent. We also need to factor in when it was rented, and when it was returned.

The main information we want to extract from all these tables is the customer ID number, customer’s full name, email address, the number of times they were late in returning a movie, and how much they owe for being late. This requires several table combinations because we need to calculate the number of overdue payments and the rent by the movie.

Here is a visual break down of what we need from each table:

And here is a breakdown of what the image means:

  1. Join the rental table and the customer table using the customer_id column
  2. Join the rental table and the payment table using the rental_id column
  3. Join the rental table and the inventory table using the inventory_id column
  4. Join the inventory table and the film table using the film_id column

Now that we checked out the order, we will combine the tables and the columns we need, let’s look at our query in full.

If it looks too complicated, don’t worry. We’re going to break it down into six simple steps. :)

1. Use JOIN in the FROM function to join five tables: rental table, customer table, payment table, inventory table, film table.

We usually start with SELECT, but this time we will begin with FROM.. We’re doing this because we want to define a nickname (alias) for all our tables first, which will make writing our SELECT function later on much more intuitive and easy.

So let’s write the JOIN query that combines our five tables in  FROM. We want all data, including loan/return information, overdue information, payment information, movie information, and customer information. So we will combine the tables with INNER JOIN, which does not generate any NULL values.

To save time and energy, so we don’t have to type out the name of the table every time we need to call on it, we will give every table a nickname. Here is the alias cheat-sheet:

Rental table alias ▶ ️ r
Customer table alias ▶ c
Payment table alias ▶ p
Film table alias ▶ f
Inventory table alias ▶ i

Combine the tables in the JOIN order above and note the common column that combines each table after ON. Great! We’ve combined all the tables to get the information we need.

2. Add an AND function to the JOIN query to get the data that corresponds to the overdue period.

We already joined the film table, but we also want a calculation result from the film table. We can do this using AND.

To get an accurate time period of how long a customer was late in returning a movie, we’re going to use DATEDIFF to calculate the difference between the day the movie was rented out and the day it was returned. If you forgot how to use DATEDIFF, check out our previous lesson on it.

⭐️ Don’t forget to utilize the ‘r’ alias for the rental table here.

3. Specify the columns we want to view for customer information (full name, email) in the SELECT function

Using the aliases we already established, we can clarify the columns we want to see in SELECT.

Start with the customer ID, which is from the customer table (alias c). Next, connect the first and last names of the customers using CONCAT. Put a space between the names using quotations and rename the column as customer_name. Last, add in the customer email.

⭐️ Keep in mind that all these columns are from the customer table, so we need to add the alias c. before each column name.

4. Count of the number of delayed movie returns per customer and view them with the SELECT function!

With information from the rental table, we can find the number of delayed returns of movies per customer. Using the COUNT function, count all the rental IDs from the rental table (alias r). Rename this column overdue_amount.

🔑 Because we only joined the long rental periods through JOIN, we will only get the number of overdue rentals.

⭐️ GROUP BY customer ID to organize your results. Don’t forget to use the alias of the customer table (c.)

5. Find the total late fee amount owed per customer for their late returns in the SELECT function.

To calculate the late fee, we need to find the difference between the actual rent paid for each movie that is past due and the specified standard rent. Use SUM to calculate the amount from the payment table (p.amount) subtracted (-) by the rental rate from the films table (f.rental_rate).

⭐️  GROUP BY is also critical to this calculation because it helps better organize the total sum of past due fees per customer ID.

6. Order the total amount of late fees and late payment calculated per customer in the order of low fees to high fees owed.

In ORDER BY, sort by the overdue_number column we created in step 4. Don’t forget to close the query with a semicolon ( ; ).

We can now execute the query and view our data!

Here is the full query if you want to  copy & paste it:

SELECT c.customer_id, CONCAT(c.first_name, “, “, c.last_name) AS customer_name, c.email, COUNT(r.rental_id) as overdue_number, SUM(p.amount-f.rental_rate) as overdue_amount 
FROM rental r 
INNER JOIN customer c ON r.customer_id = c.customer_id 
INNER JOIN payment p ON r.rental_id = p.rental_id 
INNER JOIN inventory i ON r.inventory_id = i.inventory_id 
INNER JOIN film f ON i.film_id = f.film_id 
AND f.rental_duration < DATEDIFF(r.return_date, r.rental_date) 
GROUP BY c.customer_id 
ORDER BY overdue_number;

This is a good way to join several tables and calculate the information we need from all of them. Using the  JOIN function, we're able to accurately view each customer's data and how much they owe our imaginary movie store in late fees. We can also see how many times they were late in returning a movie! 🤗