So close to getting a big stored proc to work, just this last problem with CROSS APPLY. Can you help?

Question

The below cross apply sql statement is the only part of a stored proc i cannot get to work. The error says that there is incorrect syntax near the keyword 'ON'

Can anyone see the problem?

The select statement works fine if that helps at all.

CROSS APPLY (
SELECT tbrm_Votes.ArticleID, AVG(tbrm_Votes.Truth), AVG(tbrm_Votes.Relevance)
FROM tbrm_Votes
GROUP BY tbrm_Votes.ArticleID
) AS Votes(ArticleID,Truth,Relevance)
ON tbrm_Article.ArticleID = tbrm_Votes.ArticleID

Answer 1

A CROSS APPLY is similar to a CROSS JOIN in that it is a cartesian product join showing all combinations of all rows from both sides.

Therefore there is no "ON" part of a CROSS APPLY.

What you might be after is to perform a WHERE clause within your subquery? Maybe something like this? However, in this instance it pretty much negates the GROUP BY so would probably need more information about your query / tables to help.

CROSS APPLY (
SELECT tbrm_Votes.ArticleID, AVG(tbrm_Votes.Truth), AVG(tbrm_Votes.Relevance)
FROM tbrm_Votes
WHERE tbrm_Article.ArticleID = tbrm_Votes.ArticleID
GROUP BY tbrm_Votes.ArticleID
) AS Votes(ArticleID,Truth,Relevance)

Answer 2

cross apply is a full join and doesn't need the ON statement. It multiplies the left hand side table by the Rhs table