How can I make this query easier to write

Question

I have a query that pulls up questions from one table and answers from another.

SELECT 
  questions.question, 
  questions.answers, 
  (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 1 
          GROUP BY answer) as ans1, 
  (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 2 
          GROUP BY answer) as ans2 
FROM questions 
WHERE questions.id = 1

While this works I don't like the idea of adding an extra subquery for each answer (questions.answers is a comma-seperated string of potential answers). It's do-able but I'm sure there must be a better way. The main thing is that different questions have different numbers of answers.

Is there a better way to do this or is this an acceptable way of doing things? I'd imagine multiple subselects in a query could have a (small) performance hit in the future (not that I'm performance testing yet).

If it's applicable I don't expect to have more than 5 answers per question.

Answer 1

It looks pretty good to me. You could leave out the "group by" clauses on the subqueries since you're only selecting one value of "answer" anyway.

I suspect that there may be something screwy with your schema if you are saving a list of possible answers with every single answer. That should probably be in a separate table with each answer represented by a single row. Delimiter separated strings are a big code smell in database design, as are duplicates of data that you expect to be the same.

Answer 2

SELECT q.question, q.answers,
  SUM(a.answer = 1) AS ans1,
  SUM(a.answer = 2) AS ans2
FROM questions q
 LEFT OUTER JOIN answers a ON (q.id = a.question_id)
WHERE q.id = 1
GROUP BY q.id;

Answer 3

Could you not use?

SELECT 
  questions.question, 
  questions.answers, 
  COUNT(answer)

FROM questions inner join answers
on questions.id = answer.question_id
WHERE questions.id = 1
GROUP BY
questions.question, 
questions.answers

You'd get rows instead of columns, but similar results.

Answer 4

maybe?

SELECT 
    questions.question, 
    questions.answers, 
    (case when answers.answer = 1 then COUNT(answer) end ) as ans1, 
    (case when answers.answer = 2 then COUNT(answer) end ) as ans2 
    FROM questions left join answers on answers.question_id = questions.id
    WHERE questionss.id = 1
    group by questions.id

Answer 5

The standard way for linking multiple rows in one table to one or more rows in another table is to use a link table. Such a table would store in each of its rows an ID from one table and the ID from the other table that is related to the first one.

For your questions and answers, storing the possible answers to a question in a link table (that I might call question_answered) might looks like this:

question_id | answer_id

1 1

1 2

2 3

4 4

4 5

4 6 `

As you can see, question 1 has two possible answers, question 2 has 1 possible answer, question 3 has gone unanswered while question 4 received 3 different answers.

While I'm not sure what "answer = 1" or "answer = 2" mean in your context, using a link table counting all the answers to a question is easy as:

SELECT question, count(answer_id) FROM questions AS q INNER JOIN question_answered AS qa ON qa.question_id = q.id) GROUP BY q.id

Answer 6

      SELECT    questions.question,
                       questions.answers,
                       A.answer,
                       COUNT(A.answer)
        FROM      questions Q
LEFT JOIN      answers A
            ON      Q.id = A.question_id
     WHERE      questions.id = 1
          AND      A.answer IN (1,2)
GROUP BY     A.answer