multiple LEFT JOINS with multiple COUNT()

Question

So I'm working on a website with a lot of many to many data base relationships, and because I am still a bit new to querying relational databases, I am struggling a bit. so I have several tables that I am trying to get related data from. I'm not going to go into the entire data base, because it is quite large, I am trying to get the number of comments on all of a particular users posts and the number of likes that post has, and the way I was going about it was using a LEFT JOIN like so

  SELECT Post.idPosts, Post.Title, Post.Date_poste,
  COUNT(Post_has_Comments.Post_idPost), 
  COUNT(Post_has_Likes.Post_idStories)
  FROM Post
  LEFT JOIN Post_has_Comments ON Post.idPost = S    
Post_has_Comments.Post_idStories
LEFT JOIN Post_has_Likes ON Post.idPost = Post_has_Likes.Post_idStories
WHERE Post.idUsers  =  1

But the problem I'm running into is if there are no comments or no likes this will return an error, in addition if there is a like or a comment it will return the highest number in both fields, for instance if there are 3 comments on a post and 1 like it will return 3 in the like field too, because it is counting the number of rows it is returning i guess. so my question is how do I actually get the real number of likes and comments and put in that field, and have it return 0 if there are none instead of an error?

Answer 1

SELECT Post.idPosts, Post.Title, Post.Date_poste,
    coalesce(cc.Count, 0) as CommentCount,
    coalesce(lc.Count, 0) as LikeCount
FROM Post p 
left outer join(
    select Post_idPost, count(*) as Count
    from Post_has_Comments
    group by Post_idPost
) cc on p.idPost = cc.Post_idPost
left outer join (
    select Post_idStories, count(*) as Count
    from Post_has_Likes
    group by Post_idStories
) lc on p.idPost = lc.Post_idStories
WHERE p.idUsers = 1