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
For example, if we have a site where you could review tv shows, we could have 3 tables.
users table, with
tv table, with
genre columns. And a
reviews table, with
In this example,
users are associated via the
reviews table, which is the
reviews table connects the
tv table and
user table via the
user_id foreign keys.
JOIN the tables the same way we do with a one to many relationship, except we have multiple
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
RIGHT JOIN's you will need to consider the order you
JOIN on so you get the correct tables on the correct sides!