CODEWITHSUNDEEP
mysqlsqlpostcomments
user1797770
user1797770

Reputation: 159

Select all post and its comments for a specific user

i have a common situation here. I want to select all posts and its comments for a specific user. For example:

 1)

   Post:
     1(PostId), SomeTitle, SomeText
     2(PostId), SomeTitle, SomeText
   Comments:
     1, 1(PostId), CommentTitle, CommentText, UserId(1 - from another table)
     2, 1(PostId), CommentTitle, CommentText, UserId(1 - from another table)
     3, 2(PostId), CommentTitle, CommentText, UserId(2 - from another table)
     4, 2(PostId), CommentTitle, CommentText, UserId(2 - from another table)

I want to select, for example, first post in one result set and its comments in another result set by user id. In short, i would like to select all user commented posts and its comments. Can anyone help me? I was thinking to create a SP with userId and postId as parameters, but i had issues.

Upvotes: 0

Views: 1419

Answers (2)

Alex
Alex

Reputation: 371

SELECT p.SomeTitle, p.SomeText, c.CommentTitle, c.CommentText, c.UserID
FROM post AS p
LEFT JOIN Comments AS c
        ON c.PostId = p.PostId

if you want to add information about the use who commented from another table (let's call it userTable), you can add this:

LEFT JOIN userTable AS uT
       ON uT.UserId = c.UserId

This code should return you ALL the posts even these with no comments + the ones with comments associated with their respective posts

Upvotes: 0

JSB
JSB

Reputation: 54

This selects all posts

SELECT * FROM Post WHERE PostID IN (SELECT PostID FROM Comments WHERE UserID = 1);

This select all posts and comments:

SELECT * FROM Post AS P, Comments AS C WHERE C.PostID = P.PostID AND C.UserID = 1 group by C.CommentId;

(Not tested, but should work)

Upvotes: 1

Related Questions