SQL Query selecting users personal rating, or if that doesn't exist the average rating

I have two tables:

Recipes

RecipeId | UserId | Name | etc.

and

RecipeRatings

RecipeRatingId | RecipeId| UserId | Rating| Comment

I want to select a list of recipes and include the average rating. Something like this would work:

SELECT
    Recipes.RecipeId,
    Name,
    AVG(RecipeRatings.Rating) AS AverageRating
FROM Recipes
INNER JOIN RecipeRatings
ON RecipeRatings.RecipeId = Recipes.RecipeId
GROUP BY Recipes.RecipeId, Recipes.Name
ORDER BY AverageRating DESC

But what I'd really like is to get the Average OR the current users rating. Seems like I shouldn't bother showing the average if the user rated it higher or lower themselves.

So given a parameter @userId, is there a way to select a list of recipes along with the current users rating if there is one, otherwise the average (if there is one) from the RecipeRatings table?

It would also be fine to have AverageRating and MyRating columns and it will be up to the client side to display it correctly.

Solution

You could try this:

SELECT
    R.RecipeId,
    R.Name,
    AVG(RR.Rating) AS AverageRating,
    MAX(CASE WHEN RR.UserId = @userId THEN RR.Rating END) AS MyRating
FROM Recipes AS R
LEFT JOIN RecipeRatings AS RR
ON RR.RecipeId = R.RecipeId
GROUP BY R.RecipeId, R.Name
ORDER BY AverageRating DESC

If you want to show MyRating if it is not null, otherwise AverageRating you could look at the COALESCE function.

COALESCE(MAX(CASE WHEN RR.UserId = @userId THEN RR.Rating END),
         AVG(RR.Rating) AS ActualRating

I also changed your INNER JOIN to a LEFT JOIN so that recipes without any ratings can still be shown in the results.