[SOLVED] Help With SELECT COUNT Query

[jyushinx]

Hi,

 

I am having an issue with using the SELECT statement with a COUNT. As far as the database goes. There is an articles table and a response table. Any article can have any number of responses.

 

What I am trying to do is pull an article from the articles table. Part of the information I need however, is the total number of responses. I am trying to do this with a COUNT statement in my SELECT. Here is an example query:

 

SELECT articles.ArticleId,articles.Title,articles.SubmittedWhen,COUNT(responses.ResponseId) AS totalResponses FROM articles,responses WHERE (articles.ArticleId=responses.ArticleId) GROUP BY articles.ArticleId ORDER BY SubmittedWhen

 

Now this query works fine when an article has responses, it pulls the article info plus the number of responses. BUT when an article exists that has no responses, the query does not return it since articles.ArticleId does not equal responses.ArticleId. I'd like this to return the article info plus a 0 for the count.

 

Any ideas as to how I can do this?

 

Thanks.

[Wildbug]

When you also want something that doesn't exist, that usually calls for a LEFT JOIN.

 

SELECT
articles.ArticleId,
articles.Title,
articles.SubmittedWhen,
COUNT(responses.ResponseId) AS totalResponses
FROM
articles LEFT JOIN responses ON articles.ArticleId=responses.ArticleId GROUP BY articles.ArticleId ORDER BY SubmittedWhen

[jyushinx]

Thanks for your help.

 

That worked perfectly.