question

fashraf avatar image
fashraf asked

Count from three tables.

I have three tables. Article Article_Id Article 1 Abc LikeTbl LikeId Article_Id DateTime 1 1 12/6/2012 2 1 12/6/2012 3 1 12/6/2012 Comment CommentId ArticleId Comment DateTime 1 1 Comment 1 12/6/2015 I have tried this SELECT ar.Article , COUNT(DISTINCT lik.LikeId) AS [Count For Like] , COUNT(cmt.CommentId) AS [Count For Comment] FROM Article ar LEFT JOIN LikeTbl lik ON lik.ArticleId = ar.ArticleId LEFT JOIN Comment cmt ON cmt.ArticleId= ar.ArticleId GROUP BY ar.Article it gives me Article Like Comment ABC 2 2
tablecountmssqlrow-counts
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi. So what's the question?
1 Like 1 ·
I get 2 comments when there is only 1. It counts the number of likes and displays it both in Like and the comment count section.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
use a distinct count for the comments too: SELECT ar.Article , COUNT(DISTINCT lik.LikeId) AS [Count For Like] , COUNT(DISTINCT cmt.CommentId) AS [Count For Comment] FROM Article ar LEFT JOIN LikeTbl lik ON lik.ArticleId = ar.ArticleId LEFT JOIN Comment cmt ON cmt.ArticleId= ar.ArticleId GROUP BY ar.Article
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.