This site runs best with JavaScript enabled.Lesson 28: Joining tables using Multiple Joins.

Lesson 28: Joining tables using Multiple Joins.


Eliminate data redundancy.

One of the biggest elements of "good" database design is reducing data redundancy. This means eliminating any objects which aren't required or necessary.

Don't have a field for Date of Birth and another for Age for example. Don't hold the number of products sold by salesperson in one table and a list of every sale in another table. One element is redundant so get rid of it.

We can help achieve this by proper use of multiple tables and using foreign keys to join them.

How would that look in practice?

If we ran a fintech startup, we would have one table for Customers and another table for Accounts. Customers could have multiple accounts. Similarly, a single account could have more than one customer connected to it (like a joint mortgage or credit card). We would then have another table that shows the combinations of Customer and Account connections. Let's see it in an example.

Customers

CustomerIDCustomerName
1Steve
2Adam
3Lola
4Georgina

Accounts

AccountIDAccountType
1Mortgage
2Credit Card
3Mortgage

CustAccConn

ConnIDCustomerIDAccountID
111
221
323
432
542

We can write our SQL query to bring back the Customer Name, Account ID and Account Type for all customers and their connections to their accounts.

SELECT
Customers.CustomerName,
Accounts.AccountID,
Accounts.AccountType
FROM Customers
INNER JOIN CustAccConn
ON Customers.CustomerID = CustAccConn.CustomerID
INNER JOIN Accounts
ON CustAccConn.AccountID = Accounts.AccountID;

Quiz Time.

The Question.

As the years have went on, we have more and more Marvel movies to enjoy. And more of our favourite characters (and therefore actors) keep showing up in different movies.

Write a SQL query to bring together the movies and the actors who appeared in them - using the link table in the middle.

The Data.

The Editor.



The Answer.

Correct SQL:

Correct Output:

Share lesson