CODEWITHSUNDEEP
sqlmysqljoin
Matt
Matt

Reputation: 11317

Stuck with an SQL join query

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

Upvotes: 1

Views: 169

Answers (3)

Jon Black
Jon Black

Reputation: 16559

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

MySQL Left Join with conditional

Upvotes: 0

Stephen Wuebker
Stephen Wuebker

Reputation: 189

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

...
GROUP BY q.question_id
LIMIT 10

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

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

Upvotes: 7

Related Questions