How to select multiple fields in the same row? (MySQL)

Question

I asked a question yesterday about using MySQL to tally results to a survey.

Now my question is, if I had a table of survey questions, a table of survey choices, and a table of users answers to those survey questions, how would I select the survey question along with all the choices for that survey within the same query?

Questions Table

question_id (int)
question (text)

Choices Table

choice_id (int)
question_id (int)
choice_text (varchar)

Answers Table

answer_id (int)
question_id (int)
choice_id (int)

What SELECT should I do to get the survey question along with all the choices for that survey (known or unknown amount) all in the same query? (if possible, also do the math, found in my other question, within the same query)

I'm not so advanced with MySQL.

Thanks

EDIT: Sorry, What I meant was, I'm trying to get a SELECT statement to select the question, and all the choices corresponding to that question, in one row.

If I do something like

SELECT question_id, question, choice_id, choice_text FROM questions LEFT JOIN choices USING(question_id)

I get multiple rows, one for each choice_id.

The results should be something like

question     choice_1  choice_2  choice_3
A or B or C     A          B        C

The math part is tallying up the results to the survey, and yes, the choice_id is a PK, if that helps.

Solution 1

To get the questions and the choices for each question:

SELECT question, choice_text
FROM questions
JOIN choices
ON questions.question_id = choices.question_id

Add LEFT before JOIN if you also want questions that have no choices.

To get the counts for each answer you could do this:

SELECT question, choice_text, COUNT(answers.choice_id)
FROM questions
JOIN choices
    ON questions.question_id = choices.question_id
LEFT JOIN answers
    ON questions.question_id = answers.question_id
    AND choices.choice_id = answers.choice_id
GROUP BY questions.question_id, choices.choice_id
ORDER BY questions.question_id, choices.choice_id

To get the number of people that selected each answer as a percentage (per question) use the following query:

SELECT question, choice_text, COUNT(answers.choice_id) * 100 / questiontotal
FROM questions
JOIN (
        SELECT questions.question_id, COUNT(answers.choice_id) AS questiontotal
        FROM questions
        LEFT JOIN answers ON questions.question_id = answers.question_id
        GROUP BY questions.question_id
    ) AS answercounts
    ON questions.question_id = answercounts.question_id
JOIN choices ON questions.question_id = choices.question_id
LEFT JOIN answers
    ON questions.question_id = answers.question_id
    AND choices.choice_id = answers.choice_id
GROUP BY questions.question_id, choices.choice_id
ORDER BY questions.question_id, choices.choice_id;

Here's the testdata I used:

CREATE TABLE questions (question_id int, question nvarchar(100));
INSERT INTO questions (question_id, question) VALUES
(1, 'Foo?'),
(2, 'Bar?');

CREATE TABLE choices (choice_id int, question_id int, choice_text nvarchar(100));
INSERT INTO choices (choice_id, question_id, choice_text) VALUES
(1, 1, 'Foo1'),
(2, 1, 'Foo2'),
(3, 1, 'Foo3'),
(4, 2, 'Bar1'),
(5, 2, 'Bar2');

CREATE TABLE answers (answer_id int, question_id int, choice_id int);
INSERT INTO answers (answer_id, question_id, choice_id) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 3),
(4, 2, 4),
(4, 2, 5);

And the output I get with the last query on this data:

'Foo?', 'Foo1', 66.6667
'Foo?', 'Foo2', 0.0000
'Foo?', 'Foo3', 33.3333
'Bar?', 'Bar1', 50.0000
'Bar?', 'Bar2', 50.0000

In the update to your question you say you want to return all the values for one question on one row. I would recommend that you do not try to do this, and instead use the method I have given you above. If you need to present the data in one row to your end-user, this can be done using PHP.

Solution 2

I would suggest Mark Byers's answer, though theoretically if you need them "all in one row" just for viewing purposes but not as part of a PHP routine you can do.

SELECT question, GROUP_CONCAT(choice_text) as choice_texts
FROM questions
JOIN choices
ON questions.question_id = choices.question_id
GROUP BY question;

The output would be

'Foo?', 'Foo1,Foo2,Foo3'
'Bar?', 'Bar1,Bar2'

Note 'Foo1,Foo2,Foo3' would be returned as one column not three columns, and GROUP_CONCAT has a maximum of 1024 characters in its output by default though it can be increased. Its not recommended if the results might be large

Solution 3

Note that the Answers table probably doesn't need the QuestionID column.

This will get you the question text, choice text, and the number of answers per choice.

SELECT Questions.question, Choices.choice_text, Count(Answers.*) AS N
FROM Questions INNER JOIN Choices ON Questions.question_id = Choices.question_id
     LEFT JOIN Answers ON Choices.choice_id = Answers.choice_id
GROUP BY Questions.question_id, Choices.choice_id

Edit: unless you're very, very good at SQL, it's probably best to forget about the "all one row" part - unless the number of choices per question is not only known, but constant, and even in that case, it ain't easy. It's really a display issue, so deal with it when you display the results.