SQL Table Joining Between 3 Tables-CodePudding

I am creating a Reddit clone to practice webdev, and have decided to use a SQL DB in my backend. I have pasted the schema for each table below, but essentially I have a profiles table which gives each profile an id, a subreddits table which gives each subreddit and id, a posts table which gives each post an id, and a followed_subreddits table which serves as a junction table between profiles and subreddits.

I am able to select all posts which are from the subreddits that a given user is following, however I am failing to include the posters username in the returned result, shown below.

[![Returned result][1]][1]

The query I am using to select this data is:

SELECT DISTINCT 
    posts.title, posts.description, posts.profile_id, subreddits.id
FROM 
    followed_subreddits
JOIN 
    subreddits ON followed_subreddits.subreddit_id = subreddits.id
JOIN 
    profiles ON followed_subreddits.profile_id = profiles.id
JOIN 
    posts ON followed_subreddits.subreddit_id = posts.subreddit_id
WHERE 
    profiles.username = "kkingsbe";

What should I be doing in order to also retrieve the username of the account which created each post? The challenge appears to be that I am already referencing the profiles table when I join it to filter out the posts which are not from a followed subreddit, but I also need to use the profiles table in order to get the username from a given profile id.

CREATE TABLE `profiles` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `hashed_pw` binary(60),
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `subreddits` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `posts` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `profile_id` int NOT NULL,
    `subreddit_id` int NOT NULL,
    `title` varchar(255) NOT NULL,
    `description` varchar(8000),
    `link` varchar(1000),
    `upvotes` int DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `followed_subreddits` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `profile_id` int NOT NULL,
    `subreddit_id` int NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CodePudding user response：

When joining tables, it's good practice to use aliases for table names. That way, you can join to a single table multiple times:

SELECT DISTINCT posts.title, posts.description, posts.profile_id, subreddits.id, post_creator.username
FROM followed_subreddits
JOIN subreddits on followed_subreddits.subreddit_id = subreddits.id
JOIN profiles on followed_subreddits.profile_id = profiles.id
JOIN posts on followed_subreddits.subreddit_id = posts.subreddit_id
JOIN profiles as post_creator on posts.profile_id = post_creator.id
WHERE profiles.username = "kkingsbe";