SQL Course: Many-to-many relationship and Nested Joins.

You have learned about one-to-one and one-to-many relationships and they are quite straight forward. Many-to-Many works a bit different because you need an extra table, so called join table.

In this example we will let users like posts in the database. To achieve this functionality we need to create a many-to-many relationship because a user can like many posts and a post can have many likes.

To establish this relationship we need a join table that holds a reference to the user who likes the post and the post that is being liked.

Create a likes table that function as a join table.

CREATE TABLE Likes
(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  PostID INTEGER,
  UserID INTEGER, 
  FOREIGN KEY (PostID) REFERENCES Posts(ID),
  FOREIGN KEY (UserID) REFERENCES Users(ID)
);

Insert data to the likes table.

We add the user’s ID and the post’s ID. Ex user 1, Ben likes post 1 about sql.

INSERT INTO Likes (UserID, PostID) VALUES
  (1, 1),
  (2, 1),
  (3, 1), 
  (1, 2),
  (2, 2),
  (3, 3);

Nested Join

To be able to query data from a many-to-many relationship we need nested joins.

1. Select the fields to retrieve.
2. Use the join table as starting point, our likes table.
3. Join the related data, in our case the users and the posts.

SELECT 
u.Username, p.Title, p.Content
FROM Likes l
JOIN Users u ON l.UserID = u.ID
JOIN Posts p ON l.PostID = p.ID;

To make the result more readable let’s group users and the posts they liked together.

SELECT 
u.Username, p.Title, p.Content
FROM Likes l
JOIN Users u ON l.UserID = u.ID
JOIN Posts p ON l.PostID = p.ID
GROUP BY u.ID, p.ID;

Result:

Ben | sql | sql content
Ben | java | java content
Jim | sql | sql content
Jim | java | java content
Luk | sql | sql content
Luk | NLP | NLP content