Count from three tables.

Question

question

fashraf asked Jun 16, '15

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

table count mssql row-counts

2

10 |1200

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

David Wimbush commented · Jun 16, 2015 at 10:30 AM

Hi. So what's the question?

1 ·

fashraf commented · Jun 16, 2015 at 10:32 AM

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 ·

Answer 1 · 2015-06-16T10:42:35Z

Kev Riley answered Jun 16, '15

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

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

question