SQL Server 2008 - Conditional Range
- by user208662
Hello,
I have a database that has two tables. These two tables are defined as:
Movie
-----
ID (int)
Title (nvchar)
MovieReview
-----------
ID (int)
MovieID (int)
StoryRating (decimal)
HumorRating (decimal)
ActingRating (decimal)
I have a stored procedure that allows the user to query movies based on other user's reviews. Currently, I have a temporary table that is populated with the following query:
SELECT
m.*,
(SELECT COUNT(ID) FROM MovieReivew r WHERE r.MovieID=m.ID) as 'TotalReviews',
(SELECT AVG((r.StoryRating + r.HumorRating + r.ActingRating) / 3)
FROM MovieReview r WHERE r.MovieID=m.ID) as 'AverageRating'
FROM
Movie m
In a later query in my procedure, I basically want to say:
SELECT
*
FROM
MyTempTable t
WHERE
t.AverageRating >= @lowestRating AND
t.AverageRating <= @highestRating
My problem is, sometimes AverageRating is zero. Because of this, I'm not sure what to do. How do I handle this scenario in SQL?
