Stuck on SQL query to count rows

SELECT 
  COUNT(*) AS TotalUserVotes
FROM tblArticleVotes AS v 
INNER JOIN tblArticles AS a 
  ON v.ArticleID = a.ID 
     AND a.AuthorID = 13405

This always returns 0. I have a table tblArticles which has an AuthorID and ID (primary key for article).

I then have an article votes table, with an articleID column.

Given user 13405 I need to find out how many votes they have across all of their articles!

Just to confirm, there is currently one record in tblArticleVotes which has the articleID of 5. Article ID 5 has the author ID of 13405.

Hmmm... the AuthorID in the JOIN looks odd... maybe this works better?

SELECT COUNT(*) AS TotalUserVotes
  FROM tblArticleVotes AS v INNER JOIN
  tblArticles AS a ON v.ArticleID = a.ID
  WHERE a.AuthorID = 13405

Try changing the "AND" to "WHERE":

SELECT COUNT(*) AS TotalUserVotes
FROM tblArticleVotes AS v INNER JOIN
tblArticles AS a ON v.ArticleID = a.ID WHERE a.AuthorID = 13405

HTH

There is nothing wrong with your query. To trouble shoot this break the query up.

DECLARE @ArticleID int
SELECT @ArticleID = a.ID FROM tblArticles a WHERE a.AuthorID=13405

PRINT @ArticleID
SELECT * FROM tblArticleVotes where ArticleID=@ArticleID

try this instead:

SELECT a.AuthorID, COUNT(v.id) as votes
FROM tblArticles AS a 
LEFT JOIN tblArticleVotes AS v ON a.ID = v.ArticleID
GROUP BY a.ID
WHERE a.AuthorID = :author_id