Posted by Dan Guzman on 11/30/07 12:16

> 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

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sir Hystrix" <SirHystrix@netscape.net> wrote in message
news:474fe374$0$22307$ba620e4c@news.skynet.be...
> Hi,
>
> I am struggling with a simple query, but I just don't see it.
> I have the following example table.
>
> Table Messages
> ID Subject Reply_to
> 1 A 0
> 2 Ax 1
> 3 A 1
> 4 B 0
> 5 By 4
> 6 C 0
>
> The table holds new messages as well as replies to messages.
> Messages with Reply_to = 0 are top messages, the other messages are
> replies to a top message. The subject of a reply message does not
> necessarily have to be the same as the subject of the top message.
>
> 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.
>
> ID Subject Num_replies_to
> 1 A 2
> 4 B 1
> 6 C 0
>
> Any assistance would be greatly appreciated.

[Back to original message]