Stuck with an SQL join query

Question

I'm making a Q&A site, similar to this site and Yahoo answers. I have 3 tables - smf_members, qa_questions and qa_answers.

In this query, I want to select some fields from qa_questions, a few fields from smf_members and the number of records in ga_answers for the question_id. This is so I can have some basic info on the question, some basic info on the member, and the number of answers.

This is the query I've produced so far and it almost works, but doesn't return questions with which have no answers (ie, no records in the answers table for this question_id).

SELECT qa_questions.question_id, 
       qa_questions.question_title, 
       qa_questions.question_content, 
       qa_questions.time_asked, 
       qa_questions.question_author, 
       qa_questions.votes, 
       qa_questions.views, 
       qa_questions.pretty_url, 
       smf_members.real_name, 
       smf_members.id_member, 
       COUNT(qa_answers.question_id) AS answers 
FROM   qa_questions, 
       qa_answers, 
       smf_members 
WHERE  qa_questions.deleted = 0 
       AND smf_members.id_member = qa_questions.question_author 
       AND qa_answers.question_id = qa_questions.question_id 
ORDER  BY qa_questions.time_asked DESC 
LIMIT  10

Answer 1

Use LEFT OUTER JOIN on the qa_answers table:

SELECT q.question_id, 
    q.question_title, 
    q.question_content, 
    q.time_asked, 
    q.question_author, 
    q.votes, 
    q.views, 
    q.pretty_url, 
    m.real_name, 
    m.id_member, 
    COUNT(a.question_id) as answers 
FROM qa_questions q
inner join smf_members m on m.id_member = q.question_author 
left outer join qa_answers a on a.question_id = q.question_id 
WHERE q.deleted = 0 
ORDER BY q.time_asked DESC 
LIMIT 10 

Answer 2

A previous answer of mine might help, although it didn't get any up votes or accepted so it's probably worthless :P

http://stackoverflow.com/questions/3564781/mysql-left-join-with-conditional/3565724#3565724

Answer 3

You need a GROUP BY clause to group the number of answers per question

...
GROUP BY q.question_id
LIMIT 10