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

Question

I have two tables, for example:

#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 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 usually be faster than the subquery, but, as always, you need to profile your system:

 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 

Answer 2

 select title, NumComments = (select count(*) from comments where parentID = id) from Articles 

Answer 3

 SELECT A.ID, A.Title, COUNT(C.ID) FROM Articles AS A LEFT JOIN Comments AS C ON C.ParentID = A.ID GROUP BY A.ID, A.Title ORDER BY A.ID 

Answer 4

I would do it like this:

 select a.ID 'ArticleId', a.Title, count(c.ID) 'NumComments' from Articles a left join Comments c on a.ID = c.ParentID group by a.ID, a.Title 

This can help in resolving the issue of connecting or using an auxiliary query:

Transact-SQL - optional query or left join?

Answer 5

 SELECT Articles.ID ,Articles.TItle ,(SELECT Count(*) FROM Comments WHERE Comments.ParentId = Artices.ID) AS CommentCount FROM Articles 

Answer 6

SELECT Articles.Title, COUNT (Comments.ID) FROM Articles INNER JOIN Comments ON Articles.ID = Comments .ParentID GROUP BY Articles.Title