counting child rows?

24 Mar 2006 4:53 PM

HockeyFan

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 T_ForumTopicMessage.ForumTopicID = @ForumTopicID
GROUP BY B.ParentMessageID

SQL Server Management Studio says:
Msg 4104, Level 16, State 1, Procedure udForumTopicMessageByForumTopicID,
Line 6
The multi-part identifier "T_ForumTopicMessage.ForumTopicID" could not be
bound.

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?

24 Mar 2006 5:22 PM

ML

Take a look at this example:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html

ML

---
http://milambda.blogspot.com/