ntext join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ntext join problem

Author

Topic

stsql
Starting Member

19 Posts

Posted - 2006-03-09 : 04:40:57

Hi,

I am working on a query that isn't yet right.

The problem is that I'm using the aggregate function "count(MS.messageID) as numbPosts" which requires the other columns in the group by.

MS.message is an nText field and can not be included in a group by column... How do I get around this?

Thanks

R
ps. #URL.topicID# is a variable that resolves to an integer


SELECT TP.topicTitle
     , TP.topicSubTitle
     , MB.nickname
	 , MB.memberID
	 , MB.city
	 , MB.country
	 , MB.email
	 , MB.publicemail
	 , MB.webaddress
	 , MB.dateAdded
	 , MS.message
	 , MS.correctAnswer
	 , MS.dateAdded
     , count(MS.messageID) as numbPosts
  FROM ((
       f_topics as TP
LEFT 
  JOIN f_messages as MS
    ON TP.topicID = MS.topicID)
LEFT
  JOIN f_members as MB
    ON MS.memberID = MB.memberID)
 WHERE TP.topicID = #URL.topicID#
   AND TP.active = '1'
   AND MS.active = '1'
GROUP
    BY TP.topicTitle
     , TP.topicSubTitle
	 , MS.correctAnswer
	 , MS.dateAdded
	 , MB.nickname
	 , MB.memberID
	 , MB.jtitle
	 , MB.city
	 , MB.country
	 , MB.email
	 , MB.publicemail
	 , MB.webaddress
	 , MB.dateAdded
ORDER BY	MS.correctAnswer DESC, MS.dateAdded

Kristen
Test

22859 Posts

Posted - 2006-03-09 : 05:53:12

As you are printing individual row values from f_messages what do you want [numbPosts] to show?

If its the number of messages for the current topic then you could change:

count(MS.messageID) as numbPosts

to

(SELECT COUNT(*) FROM f_messages as MS WHERE TP.topicID = MS.topicID) as numbPosts

and obviously something similar if your criteria are a bit different

Kristen

stsql
Starting Member

19 Posts

Posted - 2006-03-09 : 06:12:03

Fantastic, that was exactly it!

Cheers Kristen,

R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources