Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts

Monday, March 19, 2012

blog select with comment count

The following sql works great when the field for my main blog message is type nvarchar but doesn't work for text which I need to convert to.

select
a.id, b.textField
count(b.a_id) as myCount
from a left join b on a.id = b.a_id
group by a.id, b.textField

What other methods could i use to get "myCount" within one sql statement?

Thanks in advance, JeffHere is more information:

using sql server 2000 (from asp.net) the following sql works great when the "textForm" 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?

Although this might be a better sql question, I am also wondering about alternative asp.net solutions including repeaters with 2 or more select statements or using an array or list.|||How about something like:

select a.id,
b.textField,
myCount = (select count(*) from b where a_id = a.id)
from a

You might need to include a COALESCE function around the correlated subquery to check for NULL and send back a zero.|||it worked! thanks!

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

Friday, February 10, 2012

BIT help

I need to conver numbers from base 16 or 10 to 2 . For example if you now a function to convert 15 to F or 15 to 00001111.
I need sql function if exists .
Thanks for help.select case when @.i < 10 then convert(varchar(1), @.i) else char(ascii('A') + @.i - 10) end