Mysql JOIN counts

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.

These two tables have no relationship with each other. Did you miss a table by any chance? — z-boss, Mar 31 '10 at 14:36
As stated, there is not enough information to join these two tables. You need some way to tie back an answer to the question that it is for, such as a question_id in `Answers`. — Justin Ethier, Mar 31 '10 at 14:36
`nranswers` determines how many answers the user can choose from. If it's `5`, then the user has five answers to choose from. — cmptrgeekken, Mar 31 '10 at 14:46

Marcus Adams · Accepted Answer · 2010-03-31T16:40:34.137

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

Already did that, but it doesn't show me the replies that were not chosen. For example if we have 25 users with the answer 1, it shows me : 1-25, and I need it to echo 2-0 , 3-0, 4-0, 5-0 too. If the nranswers is 3, it must show 1-25, 2-0, 3-0, so I need to limit it somehow, and to show the zero's too — FinalDestiny, Mar 31 '10 at 14:51
@FinalDestiny, I updated my answer with a way to accommodate this. — Marcus Adams, Mar 31 '10 at 16:42

score 0 · Answer 2 · edited May 23 '17 at 11:47

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.

Mysql JOIN counts

2 Answers2

Linked