counting child rows?

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

--
24 Mar 2006 07: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
AS
SELECT
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?

SQLUSA
New Member
New Member

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

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com


Acceptable Use Policy
---