select statement for blog

[jmoeller18]

using sql server 2000 (from asp.net) the following sql works great when the message field is of type nvarchar but doesn't work for the text type.

I don't believe nvarchar will work for my needs since it is limited to 8000 (4000 due to double storage). I believe I will need to use the text type.

select bm.message_id,
bm.title,
bm.display_date,
bm.message,
count(bc.comment_id) as commentCount
from blog_messages as bm LEFT JOIN blog_comments as bc
on bm.message_id = bc.message_id
where bm.active_flag = 1
group by bm.message_id, bm.title, bm.display_date, bm.message
order by display_date DESC

The purpose of the select statement is to display a list of blog entries that includes the number of comments on each entry. An example of this in cold fusion is found at

http://www.camdenfamily.com/morpheus/blog/

With my limited knowledge of the asp.net repeater control, I'm not sure how to integrate 2 select statements (1st for blog content, 2nd for comment count). It seems the fastest way to get all of the information I want is to use a stored procedure using either a temp table or a cursor to compile all of the data together. However this method may forcee me to select the count from table b for each record of table a which could be time consuming.

Is there any way to achieve this goal with one select using the union statement or another type of join?

Jeffrey E. Moeller

http://www.JeffreyMoeller.com

 

[swampBoogie]

select  
   bm.message_id,
   bm.title,
   bm.display_date,
   bm.message,
   (select count(*) from blog_comments
     where message_id = bm.message_id) as commentCount
from blog_messages as bm 
where     bm.active_flag = 1
order by display_date DESC

 

[jmoeller18]

Thanks, I didn't realize it was that easy!

Jeffrey E. Moeller

http://www.JeffreyMoeller.com