Posted by Sir Hystrix on 11/30/07 13:30

Dan Guzman wrote:
>> What I would like to have returned is this: a list of messages where
>> Reply_to = 0 and the number of replies to this message.
>
> A subquery like the example below is one method.
>
> SELECT
> m.ID,
> m.Subject,
> (SELECT COUNT(*)
> FROM dbo.Messages
> WHERE Reply_to = m.ID
> ) AS Num_replies_to
> FROM dbo.Messages AS m
> WHERE Reply_to = 0
>

I knew it was simple. It had to be simple. I just didn't see it.
Many thanks to both Dan and Manfred.

Cheers.

[Back to original message]