MySQL - relationships: many to many

What is a many to many relationship?

When it comes to data one database table can use information from another. This is what we mean when we say relationship. A many to many relationship occurs when multiple records in a table are associated with multiple records in another table.

Types of many to many relationships - Books - Authors Books can have more than one author and authors can have more than one book. Posts - Hashtags Posts can have more than one hashtag and hashtags can have more than one post. Students - Classes Students have multiple classes, and classes have multiple students

Using tables with many-to-many relationships

With many to many relationships you use a third table called a JOIN or UNION table.

For example, if we have a site where you could review tv shows, we could have 3 tables. A users table, with id, first_name and last_name columns. A tv table, with id, title, released_year and genre columns. And a reviews table, with id, rating, tv_id and user_id columns.

In this example, tv and users are associated via the reviews table, which is the JOIN or UNION table. The reviews table connects the tv table and user table via the tv_id and user_id foreign keys.

We JOIN the tables the same way we do with a one to many relationship, except we have multiple JOIN statements.

SELECT title, rating, first_name, last_name
FROM reviews
JOIN users ON reviews.user_id = users.id
JOIN tv ON reviews.tv_id = tv.id;

Note that with an INNER JOIN, or JOIN, the order that you JOIN on doesn't matter, but with LEFT and RIGHT JOIN's you will need to consider the order you JOIN on so you get the correct tables on the correct sides!