Clever Geek Handbook

Multiple LEFT JOINS with multiple COUNT ()

So, I'm working on a site with many relationships many databases, and because I'm still a bit new to relational databases, I'm struggling a bit. so I have several tables from which I am trying to get related data. I am not going to go into the whole database because it is quite large, I am trying to get the number of comments on all specific posts from users and the number of posts they liked, and the way I went about it using LEFT JOIN like this

  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 am facing is if there are no comments or not, this will return an error, besides, if there is a similar or comment, it will return the largest number in both fields, for example if there are 3 comments on the post and 1 like it will return 3 in a similar field because it is counting the number of rows it returns I guess. so my question is how do I actually get the real number of likes and comments and put them in this field and does it return 0 if there is no error instead?

+3


source to share


3 answers 


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


+7


source


Instead of using count (), you can have a nested query that returns a counter:



 SELECT Post.idPosts, Post.Title, Post.Date_poste,
  select COUNT(Post_idPost) from Post_has_Comments where Post_has_Comments.Post_idPost = Post.idPosts, 
  ...
+2


source


You have to rename your result from columns that have count () like this

SELECT Post.idPosts, Post.Title, Post.Date_poste,
  COUNT(Post_has_Comments.Post_idPost) AS comments_count, 
  COUNT(Post_has_Likes.Post_idStories) AS likes_count
  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
-1


source






More articles:


All Articles