sql - left join - count sql - left join - count sql sql

sql - left join - count

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 cON c.ArticleID=a.ArticleID ORDER BY 1


   SELECT a.articleid,           COUNT(*) AS num_comments     FROM ARTICLES aLEFT 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.

This should do it..

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