The below stored proc returns data from a number of related tables and also does a cross apply on the votes table. This cross apply returns an average of all truthfulness values in the truthid column associated with a particular articleid and the same for the relevanceid column. This works great except for when there are no votes yet cast for a particular articleid. In this case the stored proc returns nothing at all. Can anyone think of a good way to solve this issue given I don't want to fudge a record in the votes table?
( @ArticleID int )
AS
BEGIN
WITH Article AS
(
SELECT
tbrm_Article.ArticleID,
tbrm_Article.CountryID,
tbrm_Article.CategoryID,
tbrm_Article.Title,
tbrm_Article.ArticleDetail,
tbrm_Article.Source,
tbrm_Article.ArticleDateTimeAdded,
tbrm_Article.UserId,
tbrm_Article.ViewCount,
tbrm_Article.CommentCount,
tbrm_CountryList.CountryName AS CountryName,
tbrm_CategoryList.CategoryName AS CategoryName,
aspnet_Users.UserName AS UserName,
Truthfulness,
Relevance
FROM
tbrm_Article INNER JOIN
tbrm_CountryList ON tbrm_Article.CountryID = tbrm_CountryList.CountryID INNER JOIN
tbrm_CategoryList ON tbrm_Article.CategoryID = tbrm_CategoryList.CategoryID INNER JOIN
aspnet_Users ON tbrm_Article.UserID = aspnet_Users.UserID
CROSS APPLY (
SELECT tbrm_Votes.ArticleID, AVG(tbrm_Votes.TruthID), AVG(tbrm_Votes.RelevanceID)
FROM tbrm_Votes
WHERE tbrm_Article.ArticleID = tbrm_Votes.ArticleID
GROUP BY tbrm_Votes.ArticleID
) AS Votes(ArticleID,Truthfulness,Relevance)
WHERE
tbrm_Article.ArticleID = @ArticleID)
SELECT * FROM Article
END