CODEWITHSUNDEEP
mysqlsql
user3190685
user3190685

Reputation: 33

sql - joining 3 tables on same column

The tables are:

blogs
blog_id, user_id, blog_content

users
user_id, user_name

comments
comment_id, blog_id, user_id, comment_content

What I'd like to do is get the: blog_content, the user_name of the person that created the blog, the comment_content associated with the particular blog, and the user_name of the writers of the comments. I have no idea how to get the last one, the user_name of the comment writers. I have this so far:

SELECT blogs.user_id, blogs.blog_id, blogs.blog_content, 
            users.user_id, users.name, 
            comments.comment_id, comments.blog_id, comments.user_id, comments.comment_content 
            FROM blogs 
            LEFT JOIN users ON users.user_id = blogs.user_id 
            LEFT JOIN comments ON comments.blog_id = blogs.blog_id 
            WHERE blogs.blog_id = 2

How can i also join the tables comments and users in this query, and get both the user_name of the blog creator, and of the comments writers?

Upvotes: 1

Views: 2610

Answers (3)

Jon Senchyna
Jon Senchyna

Reputation: 8037

You can have multiple joins on the same table. Each join will retrieve another set of data from that table. In order to include the same table multiple times in a single query, you must create an alias for at least one of the copies so that SQL knows which one you are referring to when attempting to retrieve data from their columns.

Since you need to get the username for the users who wrote the comments, I would recommend joining again on the users table, as follows:

SELECT blogs.user_id,
       blogs.blog_id,
       blogs.blog_content, 
       users.user_id,
       users.name, 
       comments.comment_id,
       comments.blog_id,
       comments.user_id,
       comments.comment_content ,
       comment_writers.name as CommentUserName
FROM blogs 
LEFT JOIN users ON users.user_id = blogs.user_id 
LEFT JOIN comments ON comments.blog_id = blogs.blog_id
LEFT JOIN users AS comment_writers ON comment_writers.user_id = comments.user_id
WHERE blogs.blog_id = 2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need to join the comments table back to another copy of the users table:

SELECT b.user_id, b.blog_id, b.blog_content, 
       u.user_id, u.name, 
       c.comment_id, c.blog_id, c.user_id, c.comment_content ,
       uc.name as commentor_name
FROM blogs b LEFT JOIN
     users u
     ON u.user_id = b.user_id LEFT JOIN
     comments c
     ON c.blog_id = b.blog_id LEFT JOIN
     comments uc
     on c.user_id = uc.user_id
WHERE b.blog_id = 2;

In this query, all the tables have table aliases. This helps make the query more readable. Also, the table aliases are needed to distinguish the two references to users. One reference is for the blog owner and the other is for the comment writer.

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT blogs.user_id, blogs.blog_id, blogs.blog_content, 
            users.user_id, users.name, 
            comments.comment_id, comments.blog_id, comments.user_id, comments.comment_content 
            FROM blogs 
            LEFT JOIN users ON users.user_id = blogs.user_id 
            LEFT JOIN comments ON comments.blog_id = blogs.blog_id 
            LEFT JOIN comments c  ON c.user_id=users.user_id
            WHERE blogs.blog_id = 2

You can again do a join comments table this time giving it an alias to distinguish it from previous comment table .

Upvotes: 0

Related Questions