On Sat, 27 Mar 2004 21:27:52 +0100, Guoqi Zheng wrote:

>Dear Sir,
>
>I have the following two tables for a forum.
>
>tblTopic
> TopicId
> TopicContent
> ....
>
>tblReply
> ReplyId
> TopicId
> ReplyContent
>
>I tried to create the following SQL, I need to return a count of ReplyId to
>that statement.
>
>SELECT tblTopic.TopicId, tblTopic.TopicContent, Count(tblReply.ReplyId) as
>Posts FROM tblTopic INNER JOIN tblReply on tblTopic.TopicId =
>tblReply.TopicId
>Group By tblTopic.TopicId, tblTopic.TopicContent
>
>The above SQL gives me an error of "The text, ntext, and image data types
>can not be compared or sorted, except when using IS NULL or LIKE operator"
>
>And suggestion???
>
>Thanks in advanced.

> On Sat, 27 Mar 2004 21:27:52 +0100, Guoqi Zheng wrote:
>
> >Dear Sir,
> >
> >I have the following two tables for a forum.
> >
> >tblTopic
> > TopicId
> > TopicContent
> > ....
> >
> >tblReply
> > ReplyId
> > TopicId
> > ReplyContent
> >
> >I tried to create the following SQL, I need to return a count of ReplyId
to
> >that statement.
> >
> >SELECT tblTopic.TopicId, tblTopic.TopicContent, Count(tblReply.ReplyId)
as
> >Posts FROM tblTopic INNER JOIN tblReply on tblTopic.TopicId =
> >tblReply.TopicId
> >Group By tblTopic.TopicId, tblTopic.TopicContent
> >
> >The above SQL gives me an error of "The text, ntext, and image data types
> >can not be compared or sorted, except when using IS NULL or LIKE
operator"
> >
> >And suggestion???
> >
> >Thanks in advanced.
>
> SELECT tblTopic.TopicId, tblTopic.TopicContent, T.Posts
> FROM tblTopic
> INNER JOIN (SELECT TopicId,
> Count(ReplyId) as Posts
> FROM tblReply
> GROUP BY TopicId) AS T
> ON T.TopicID = tblTopic.TopicId
>
> (untested)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)