counting child rows?

Last Post 30 Jun 2007 08:15 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

24 Mar 2006 08:24 AM
I've got a query where I have to find all messages for a particular topic,
and then I need a count of all child messages for each message. Here's what
I have so far:

ALTER PROCEDURE [dbo].[udForumTopicMessageByForumTopicID]
@ForumTopicID int
A.ForumTopicMessageID AS "ForumTopicMessageID",
A.ForumTopicID AS "ForumTopicID",
A.ContactID AS "ContactID",
A.MessageTitle AS "MessageTitle",
A.MessageText AS "MessageText",
A.ApprovedInd AS "Approved",
A.ReviewedInd AS "ReviewedInd",
A.ParentMessageID AS "ParentMessageID",
A.OwnerCompany AS "ForumTopicMessageOwnerCompany",
A.CreateUser AS "ForumTopicMessageCreateUser",
A.UpdateUser AS "ForumTopicMessageUpdateUser",
A.CreateDate AS "ForumTopicMessageCreateDate",
A.UpdateDate AS "ForumTopicMessageUpdateDate",
'('+COUNT(B.ParentMessageID)+')' As "ChildResponseCount",
(T_Contact.Lastname + ', ' + T_Contact.Firstname) As "ContactName"
FROM [T_ForumTopicMessage] A
INNER JOIN [T_Contact] ON [T_Contact].ContactID = A.ContactID
INNER JOIN [T_ForumTopicMessage] B On B.ParentMessageID = A.ForumTopicMessageID
WHERE A.ForumTopicID = @ForumTopicID
GROUP BY B.ParentMessageID

SQL Server Management Studio says:
Msg 8120, Level 16, State 1, Procedure udForumTopicMessageByForumTopicID, Line 6
Column 'T_ForumTopicMessage.ForumTopicMessageID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'd like to get beyond this, just to find out if the join on itself will
even work.
Or maybe someone has a better way to do this?

New Member
New Member

30 Jun 2007 08:15 PM
If you are testing the JOIN only, just comment out the GROUP BY line.

Kalman Toth, Database Architect
SQL Server 2005 Training -

Acceptable Use Policy