SQL Query to get a row, and the count of associated rows

Question

I have two tables, like this:

#Articles:
ID | Title
1    "Article title"
2    "2nd article title"

#Comments:
ID | ParentID | Comment
1    1          "This is my comment"
2    1          "This is my other comment"

I've always wanted to know, what is the most elegant way to get the following result:

ID | Title |          NumComments
1    "Article title"      2
2    "2nd article title"  0

This is for SQL Server.

Answer 1

This will normally be faster than the subquery approach, but as always you have to profile your system to be sure:

SELECT a.ID, a.Title, COUNT(c.ID) AS NumComments
FROM Articles a
LEFT JOIN Comments c ON c.ParentID = a.ID
GROUP BY a.ID, a.Title