SQL statement to return selected records with a count

[Boblevien]

I have a table in a database which contains messages posted to a forum. Each message has a msgID and a parentMsgID.

I want to create a SQL statement which will select all the opening messages (where parentMsgID =0) with a COUNT of all the replies to that original message.

Has anyone any ideas?

 

[tlbroadbent]

Select a.ParentMsgID, a.Msg, b.ReplyCnt
From Table1 Inner Join
(Select ParentMsgID, count(msgID) As ReplyCnt
From Table1 Where ParentMsgID>0
Group By ParentMsgID) As b
On a.ParentMsgID=b.ParentMsgID
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Guest_imported]

I am trying to query a database of titles. Does anyone know of a way to ignore articles (the, a , an) when querying based on the first letter of the title?

 

[tlbroadbent]

malia2,

Your question should have been posted as a new thread rather than a continuation of an existing thread.

You could add additional criteria such as

And left(title,4)<>'the '
And left(title,3)><'an '
And left(title,2)<>'a '

This is not an elegant solution but without more info it will be hard to find the appropriate T-SQL. It will be easier to determine how to answer your question if you post your query.

Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Boblevien]

Hi Terry,

Thanks very much for that - it does eaxactly what I needed. Spot on.

Cheers,

Bob Levien