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
Use:
SELECT a.articleid, COUNT(*) AS num_comments FROM ARTICLES aLEFT JOIN COMMENTS c ON c.articleid = a.articleid GROUP BY a.articleidWhatever 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