GROUP BY and nText

Hi Everyone,

I was wondering if you could help with the following. Basically I have a database of Blog posts. On particular page say I want to list all the posts, easy. Now, if I want to list the posts but also have a column that also counts the number of comments left for that particular post (Comments stored in another table) then I can get this to work. The problem I am having is that the SQL I am using (below) fails if I include the column PostContent as its nText. I need it included as this contains the posts HTML. If I remove the PostContent from the SELECT and GROUP BY statements it works fine.

Any ideas on how to get this to work with PostContent included. Oh and I cant convert to varchar as it wont hold enough.

Code Snippet

SELECT P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent, Count(CommentId) AS TotalComments
FROM dbo.BlogPosts AS P LEFT OUTER JOIN dbo.BlogComments as C on P.PostId = C.PostId
WHERE P.UserId = 3
GROUP BY P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent
ORDER BY PostDate DESC

Cheers.

Hi,

why do you want to group them - you want to fetch all the blogs, right?

Simply remove grouping and add the count as subselect:

Code Snippet

SELECT P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent, (select Count(CommentId) from dbo.BlogComments as C where P.PostId = C.PostId ) AS TotalComments

FROM dbo.BlogPosts AS P

WHERE P.UserId = 3

ORDER BY PostDate DESC

If you want to show the last post grouped by user then this is another story ;-)

|||

If you use sql server 2005 then use the following query,

Code Snippet

SELECT

P.PostId

, P.UserId

, P.PostDate

, P.PostTitle

, Cast(P.PostContent as Nvarchar(Max)) as PostContent

, Count(CommentId) AS TotalComments

FROM

dbo.BlogPosts AS P

LEFT OUTER JOIN dbo.BlogComments as C on P.PostId = C.PostId

WHERE

P.UserId = 3

GROUP BY

P.PostId, P.UserId, P.PostDate, P.PostTitle, Cast(P.PostContent as Nvarchar(Max))

ORDER BY

PostDate DESC

|||

Thanks guys. Make sense now - I was totally going about it the wrong way. I have gone for Ivan's approach for simplicity.

Cheers