listing rows, counting children

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

listing rows, counting children

Author

Topic

HockeyFan
Starting Member

26 Posts

Posted - 2006-03-24 : 12:06:19

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?

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 12:13:14

Your WHERE clause has T_ForumTopicMessage.ForumTopicID but you have aliased T_ForumTopicMessage A and B in your FROM

HockeyFan
Starting Member

26 Posts

Posted - 2006-03-24 : 12:17:22

quote:
Originally posted by rpcutts

Your WHERE clause has T_ForumTopicMessage.ForumTopicID but you have aliased T_ForumTopicMessage A and B in your FROM

I fixed that, and now get an error.


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

The error is:
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.

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 12:22:38

Every item in SELECT must be an aggregate function i.e. SUM() COUNT() or in the GROUP BY clause

HockeyFan
Starting Member

26 Posts

Posted - 2006-03-24 : 12:25:24

I'm new to this. Explain what I must do to get this to work.

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 12:34:52

See what this gives you:

ALTER PROCEDURE [dbo].[udForumTopicMessageByForumTopicID]
    @ForumTopicID int
AS
SELECT	Parent.ForumTopicMessageID,
	Parent.ForumTopicID,
	Parent.ContactID,
	Parent.MessageTitle,
	(COUNT(Child.ParentMessageID)) As ChildResponseCount

FROM	T_ForumTopicMessage Parent
	INNER JOIN T_ForumTopicMessage Child ON Child.ParentMessageID = Parent.ForumTopicMessageID

WHERE	Parent.ForumTopicID = @ForumTopicID

GROUP BY	Parent.ForumTopicMessageID,
		Parent.ForumTopicID,
		Parent.ContactID,
		Parent.MessageTitle

HockeyFan
Starting Member

26 Posts

Posted - 2006-03-24 : 12:44:03

Okay. I have the following, and it sort-of works (sort of):


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",
    '('+CAST(COUNT(B.ParentMessageID) As VARCHAR(10))+')' 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, A.ForumTopicMessageID, A.ForumTopicID, A.ContactID, A.MessageTitle, A.MessageText, A.ApprovedInd,
A.ReviewedInd, A.ParentMessageID, A.OwnerCompany, A.CreateUser, A.UpdateUser, A.CreateDate, A.UpdateDate, T_Contact.LastName,
T_Contact.FirstName

It returns one row, and a proper count that shows that it has a child row. However, it should also pick up a row that doesn't have a child. That row matches the WHERE clause. Before trying to add the count, it was properly working.
So if I can figure out how to get that working, so it picks up rows that have no child row, then it's working perfectly.
Any ideas?

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 12:53:59

Replace
INNER JOIN [T_ForumTopicMessage] B On B.ParentMessageID = A.ForumTopicMessageID
With
LEFT OUTER JOIN [T_ForumTopicMessage] B On B.ParentMessageID = A.ForumTopicMessageID

This should show all Parents regardless of existence of children

HockeyFan
Starting Member

26 Posts

Posted - 2006-03-24 : 12:56:49

I think this works:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[udForumTopicMessageByForumTopicID]
    @ForumTopicID int
AS
SELECT 
	MAX(ftm_parent.ForumTopicMessageID) AS "ForumTopicMessageID",
    MAX(ftm_parent.ForumTopicID) AS "ForumTopicID",
    MAX(ftm_parent.ContactID) AS "ContactID",
    MAX(ftm_parent.MessageTitle) AS "MessageTitle",
    MAX(ftm_parent.MessageText) AS "MessageText",
    MAX(ftm_parent.OwnerCompany) AS "ForumTopicMessageOwnerCompany",
    MAX(ftm_parent.CreateUser) AS "ForumTopicMessageCreateUser",
    MAX(ftm_parent.UpdateUser) AS "ForumTopicMessageUpdateUser",
    MAX(ftm_parent.CreateDate) AS "ForumTopicMessageCreateDate",
    MAX(ftm_parent.UpdateDate) AS "ForumTopicMessageUpdateDate",
    '('+CAST(COUNT(ftm_child.ParentMessageID) As VARCHAR(10))+')' As "ChildResponseCount",
    MAX(T_Contact.Lastname + ', ' + T_Contact.Firstname) As "ContactName"
FROM [T_ForumTopicMessage] as ftm_parent
INNER JOIN [T_Contact] ON [T_Contact].ContactID = ftm_parent.ContactID
Left JOIN [T_ForumTopicMessage] as ftm_child ON ftm_parent.ForumTopicMessageID = ftm_child.ParentMessageID
WHERE ftm_parent.ForumTopicID = @ForumTopicID
group by ftm_child.ForumTopicMessageID

At least, this seems to work as I expected.

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 13:02:09

I don't know why you need to use MAX()

It should only return 1 row based on the WHERE clause. So selecting the maximum doesnt make sense.

Its like asking me to tell you what the biggest number is between these brackets - [9]
instead of just asking me what the number is between the brackets

I don't think its a good idea to wrap a field in an aggregate function that has no effect.
If you take out all of the MAX() functions and put the fields in the GROUP BY clause it should still work.

HockeyFan
Starting Member

26 Posts

Posted - 2006-03-24 : 13:07:52

But I do get multiple rows. I get rows of messages, and then counts for each that have child messages.

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 13:12:52

Sorry I misread , thought it said WHERE ftm_parent.ForumTopicMessageID = @ForumTopicID

rpcutts
Starting Member

10 Posts

Posted - 2006-03-24 : 13:23:11

I would still advise to remove MAX from all of the fields and change GROUP BY to include the fields that are not aggregated:

Which is all of the fields in SELECT appart from ftm_child.ParentMessageID

ftm_parent.ForumTopicMessageID,
ftm_parent.ForumTopicID,
ftm_parent.ContactID,
ftm_parent.MessageTitle,
ftm_parent.MessageText,
ftm_parent.OwnerCompany,
ftm_parent.CreateUser,
ftm_parent.UpdateUser,
ftm_parent.CreateDate,
ftm_parent.UpdateDate,
T_Contact.Lastname,
T_Contact.Firstname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources