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.