0

I'm trying to join 3 tables - users, posts and comments to get value like post title, post author, comment title, comment author.

users (id, name)
posts (id, content, user_id)
comments (content, user_id, post_id)

Right now I have query:

SELECT posts.id AS POST_ID, posts.content AS Post, posts.user_id AS PostAuthorID,
users.name AS PostAuthorName,
comments.content AS comment, comments.user_id AS CommentsAuthorID
FROM posts
JOIN users on users.id = posts.user_id
JOIN comments ON users.id = comments.post_id;

My question is how to get author's name of each comment and how get the author of the post? Right now by doing JOIN users on users.id = posts.user_id I have post author but how to deal with comment author name? When I'm trying to add another condition to last join - JOIN comments ON users.id = comments.post_id AND users.id = comments.user_id I receive table with posts and comments created by the same user. Any help is welcome.

mr_muscle
  • 2,536
  • 18
  • 61

3 Answers3

0

user (user_id, name)
post (post_id, user_id, descrition)
comment (cm_id, post_id, content)
you join  user with post by user_id and post with comment by post_id
dzungnv38
  • 16
  • 2
  • Maybe I didn't express myself clearly - I edited my first post. So I wanna have table row called CommentName with name of each comment author name (I have user_id in comments table). Unfortunately your solution doesn't change anything because still I have comments just with user_id not with name. – mr_muscle Nov 02 '18 at 18:03
  • Try to do : select x1.post_id, name, content, coment from ( select a.id , name, content, b.id post_id from users a, posts b where a.id =b.user_id )x1, ( select a.id , content coment, b.id post_id from users a, comments b where a.id =b.user_id )x2 where x1.user_id =x2.user_id and x1.post_id =x2.post_id; – dzungnv38 Nov 02 '18 at 19:44
0
Select comments.user_id AS CommentsAuthorID
Where posts.id = x
From (posts left join comments on posts.id = comments.post_id

If you just need the comments.user_id make a join with the posts and the comments.

When you want comments.users.name the query would be:

Select comments.user_id AS CommentsAuthorID comments.users_name AS UserName 
Where posts.id = x
From (posts left join comments on posts.id = comments.post_id
Igelaty
  • 69
  • 10
  • I have comments.user_id right now. Based on that id I wanna have something like `comments.users.name`. But in one query I wanna have post author so `posts.users.name`. – mr_muscle Nov 02 '18 at 18:42
  • in the query above you just have to add these in the select clause. – Igelaty Nov 02 '18 at 19:07
0

I am pretty sure you meant:

ON posts.id = comments.post_id;

where your query currently says:

ON users.id = comments.post_id;

Then just join to users twice:

SELECT p.id AS post_id, p.content AS post, p.user_id AS post_author_id
     , pu.name AS post_author_name
     , c.content AS comment, c.user_id AS comment_author_id
     , cu.name AS comment_author_name
FROM posts    p
JOIN users    pu ON pu.id = p.user_id
JOIN comments c  ON c.post_id = p.id
JOIN users    cu ON cu.id = c.user_id;

Returns multiple rows for posts with multiple comments. One per comment. Returns nothing for posts without comments. To include posts without comments (or if any ID column can be NULL) use LEFT JOIN instead ...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This is what I'm looking for! Thank you so much! one question - should I replace every `JOIN` by `LEFT JOIN` or only when I'm joining posts with users so `JOIN users pu ON pu.id = p.user_id` ? – mr_muscle Nov 02 '18 at 21:34
  • Simplified: whenever there might be no rows qualifying to the right of the join and you still want to keep the rows to the left, use `LEFT JOIN` instead of `JOIN`. You have to either use `LEFT JOIN` as well for additional relations to the right (joining to left-joined relations), or use parentheses. Examples: https://stackoverflow.com/a/31486181/939860, https://stackoverflow.com/a/28686060/939860 – Erwin Brandstetter Nov 02 '18 at 21:47
  • Superb explanation, I will use `LEFT JOIN` instead of `JOIN`. Maybe one additional question, if I wanna have total number of comments for each post where I should insert `COUNT` ? because when I'm trying to do it in a select i.e. `c.user_id, COUNT(*) AS total_comments I've `got syntax error at or near "("` – mr_muscle Nov 02 '18 at 23:42
  • @wichru: We've already stretched *comments* here. Please ask your new question as *question*. You can always link to this one for context if that helps. – Erwin Brandstetter Nov 02 '18 at 23:45
  • Nah, they gonna lynch me. Thanks a million! – mr_muscle Nov 02 '18 at 23:58