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.
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.negativeFROM T_Review LEFT OUTER JOIN T_Review_Votes ON T_Review.ReviewID = T_Review_Votes.reviewidWHERE (T_Review.ProductID = @ProductID) AND (T_Review.lang = @lang) AND (T_Review.active = 1)ORDER BY score descWhich 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 votesBut i want the with negative votes to the bottom and the Nulls in the middleThanks 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? |
|
|
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.negativeFROM T_Review LEFT OUTER JOINT_Review_Votes ON T_Review.ReviewID = T_Review_Votes.reviewidWHERE (T_Review.ProductID = @ProductID) AND (T_Review.lang = @lang) AND (T_Review.active = 1)ORDER BY score desc[/CODE] |
|
|
mike13
Posting Yak Master
219 Posts |
Posted - 2013-04-26 : 04:40:08
|
Thanks a lot MuMu88 that did the trick |
|
|
|
|
|
|
|