sql - left join - count

Asked 2010-02-07T03:15:48.770

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.

Edit Report

1 Answer

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

answered 2010-02-07T03:32:40.313

sql - left join - count

1 Answer

Your Answer