CODEWITHSUNDEEP
sqljoincount
RHPT
RHPT

Reputation: 2650

Returning Count from database table joined with another table

I have two tables

Titles

ID Name
1  'Title 1'
2  'Title 2'
3  'Title 3'

Comments

ID TitleID Comment
1   1       'Title 1 Comment #1'
2   1       'Title 1 Commnet #2'
3   2       'Title 2 comment'

I want a recordset that returns all the titles in Titles and the number of comments for that title, as such

 Name    # Comments
 Title 1     2
 Title 2     1
 Title 3     0

I worked out how to return all the titles and each comment, but I am having difficulty returning the data with just the count of comments. (My query is below)

SELECT T.ID, T.Name, C.Comment
FROM Titles T
LEFT OUTER JOIN Comments C ON T.ID = C.TitleID
  UNION
SELECT T.TitleID, T.Name, C.Comment
FROM Titles T
RIGHT OUTER JOIN Comments C ON T.ID = C.TitleID

Any help would be greatly appreciated.

update

I forgot that I also wanted to return titles without comments. I updated the examples above.

Upvotes: 0

Views: 285

Answers (3)

Allan W
Allan W

Reputation: 590

Can also be done with a subselect.

Select T.Name,
       (Select Count(*)From Comments C Where C.TitleID=T.ID)As NumComments
  From Titles T

The advantage is that no GROUP BY is needed. This can be important if tables in your real data have a lot more columns than the example above.

Upvotes: 2

Joe Phillips
Joe Phillips

Reputation: 51200

SELECT t.Name as Title, COUNT(c.ID) as NumberOfComments
FROM Titles t
LEFT JOIN Comments c ON c.TitleID = t.ID
GROUP BY t.Name

Ideally you would want to group by the TitleID but you would have to include that in your selection as well. If you're able to ignore that column (or use it) then I would use that instead of t.Name (or alongside t.Name).

Upvotes: 3

invertedSpear
invertedSpear

Reputation: 11054

SELECT title.name, count(comments.comment) as NumComments
FROM titles,comments
WHERE title.id = comment.titleID
Group By title.name

Upvotes: 1

Related Questions