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 2008 Forums
 Transact-SQL (2008)
 SP for voting help needed

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-25 : 18:01:04
Hi All,

I got this

SELECT (T_Review_Votes.positive-T_Review_Votes.negative) AS score,T_Review.ReviewID, T_Review.CustomerName, T_Review.Rating, T_Review.Comments, T_Review_Votes.positive, T_Review_Votes.negative
FROM T_Review LEFT OUTER JOIN
T_Review_Votes ON T_Review.ReviewID = T_Review_Votes.reviewid
WHERE (T_Review.ProductID = @ProductID) AND (T_Review.lang = @lang) AND (T_Review.active = 1)
ORDER BY score desc

Which displays the customer reviews, and orders the ones with the positive votes on top.
But now i got a problem, because many reviews do not have votes
But i want the with negative votes to the bottom and the Nulls in the middle

Thanks a lot

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-25 : 18:35:29
Hard to say since you didn't show us any data or expected output... maybe COALESCE the null values to zero?
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 21:24:50
quote:
Originally posted by Lamprey

Hard to say since you didn't show us any data or expected output... maybe COALESCE the null values to zero?



The following change should work:

[CODE]

SELECT (COALESCE(T_Review_Votes.positive, 0)- COALESCE(T_Review_Votes.negative,0)) AS score,T_Review.ReviewID, T_Review.CustomerName, T_Review.Rating, T_Review.Comments, T_Review_Votes.positive, T_Review_Votes.negative
FROM T_Review LEFT OUTER JOIN
T_Review_Votes ON T_Review.ReviewID = T_Review_Votes.reviewid
WHERE (T_Review.ProductID = @ProductID) AND (T_Review.lang = @lang) AND (T_Review.active = 1)
ORDER BY score desc

[/CODE]
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-26 : 04:40:08
Thanks a lot MuMu88 that did the trick
Go to Top of Page
   

- Advertisement -