sql - left join - count

Question

suppose i have two tables. articles and comments.

when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid)

how do I do that? I can get it done, but I do not know if my way would be efficient, so i want to learn the right way.

Answer 1

SELECT 
       a.Article,
       a.ArticleID,
       t.COUNTOFCOMMENTS
FROM
       Article a
LEFT JOIN
       Comment c
ON c.ArticleID=a.ArticleID
LEFT JOIN
(SELECT ArticleID, COUNT(CommentID) AS COUNTOFCOMMENTS FROM Comments GROUP BY ArticleID) t
ON t.ArticleID = a.ArticleID

Answer 2

This should do it..

SELECT
   article_column_1, article_column_2, count( ct.articleid) as comments
FROM
   article_table at
   LEFT OUTER JOIN comment_table ct ON at.articleid = ct.articleid
GROUP BY 
   article_column_1, article_column_2

Answer 3

Use:

   SELECT a.articleid, 
          COUNT(*) AS num_comments
     FROM ARTICLES a
LEFT JOIN COMMENTS c ON c.articleid = a.articleid
 GROUP BY a.articleid

Whatever columns you want from the ARTICLES table, you'll have to define in the GROUP BY clause because they aren't having an aggregate function performed on them.

Answer 4

This should be more efficient because the group by is only done on the Comment table.

SELECT  
       a.ArticleID, 
       a.Article, 
       isnull(c.Cnt, 0) as Cnt 
FROM Article a 
LEFT JOIN 
    (SELECT c.ArticleID, count(1) Cnt
     FROM Comment c
    GROUP BY c.ArticleID) as c
ON c.ArticleID=a.ArticleID 
ORDER BY 1