Mysql JOIN problem

Question

I have 2 mysql tables :

Question with the following columns : id, question, nranswers

Nranswers must be a number from 1 to 5

And the other table is

Answers with the following columns: questionid, userid, answer .

Now the problem is that I want to get the replies for each answer for one question(id 22 let's say) .

P.S. If the nranswers is 3, the result should look like this:

(the right number means how many times the reply number was chosen)

1 - 2

2 - 8

3 - 7

If the nranswers is 5, the result should look like this:

1 - 3

2 - 8

3 - 14

4 - 19

5 - 8

Please help me out with the query, atm he's not counting the answers that weren't chosen, only the ones that were chosen at least one time.

Answer 1

I took the liberty of adding the question_id column that will be used to join each answer with a question.

Question with the following columns : id, question, nranswers
Answers with the following columns: question_id, userid, answer

Here's your query:

SELECT answer, COUNT(*) AS answer_count
FROM Answers
GROUP BY answer
WHERE question_id = 22
ORDER by answer

However, if nranswers is 3, but nobody picked 3 as their answer, it won't show. This query only shows the answers that were chosen.

Edit:

To get a count of all available answers, not just the selected ones, the simplest way (query wise) would be to get rid of the Question.nranswers column and add the table QuestionAnswers:

QuestionAnswers with the following columns: question_id, answer

The data in QuestionAnswers would like this:

quesiton_id answer
-------------------
22          1
22          2
22          3

So, you'd have all the possible answers listed for each question.

The query would then be this:

SELECT qa.answer, COUNT(a.question_id) AS answer_count
FROM QuestionAnswers qa
LEFT OUTER JOIN Answers a
  ON qa.question_id = a.question_id AND a.answer = qa.answer
GROUP BY qa.answer
WHERE qa.question_id = 22
ORDER by qa.answer

Answer 2

Taking one of the answers to this question, it looks like you need a temporary table of some sort to select a range of numbers in MySQL. It looks like this answer can scale pretty well.

Let's assume you have a maximum of ten answers to your question. You can then do something like this:

SELECT Choices.num,IF(Answers.answer IS NULL,0,COUNT(*))
FROM 
    (SELECT choice.num,Question.*
        FROM
           (SELECT 1 num
            UNION ALL
            SELECT 2 num
            UNION ALL
            SELECT 3 num
            UNION ALL
            SELECT 4 num
            UNION ALL
            SELECT 5 num
            UNION ALL
            SELECT 6 num
            UNION ALL
            SELECT 7 num
            UNION ALL
            SELECT 8 num
            UNION ALL
            SELECT 9 num
            UNION ALL
            SELECT 10 num) choice,
           Question
         WHERE Question.id=22
    ) Choices
    LEFT OUTER JOIN Answers 
       ON Answers.question_id=Choices.id AND Answers.answer=Choices.num
   WHERE Choices.num<=Choices.nranswers
   GROUP BY Choices.num;

It's messy, but it works. If you need more choices, you can do something similar to what's done in the answer mentioned above. Also, change the WHERE Question.id=22 to the ID that you need. No other change should be necessary.