How to select several fields in one line? (Databases)

Question

I asked a question about using MySQL to get poll results yesterday.

Now my question is: if I had a table of survey questions, a table of survey options and a table of user responses to these survey questions, how would I choose a survey question along with all the options for this survey within the same query?

Table of questions

question_id (int) question (text) 

Selection table

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

Answer Table

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

What should I do SELECT to ask a survey question along with all variants of this survey (known or unknown amount) in the same query? (if possible, also do the math found in my other question within the same query)

I am not so advanced with MySQL.

thanks

EDIT: Sorry, I meant that I am trying to get a SELECT statement to select a question and all the options corresponding to this question on one line.

If I'm 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 CABC 

The math counts the poll results, and yes, choice_id is PK if that helps.

Answer 1

To ask questions and options 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 to ask questions that have no choice.

To get the counts for each answer, you can 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 who chose 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 are the tests 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 that I get with the last request for this data:

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

In updating your question, you say that you want to return all the values ​​for one question on one line. I would recommend that you not try to do this, but instead use the method that I gave you above. If you need to present data in one line to the end user, this can be done using PHP.

Answer 2

I would suggest that Mark Byers answer, although theoretically, if you need them all in one line, just for viewing, but not as part of the PHP procedure 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 will be

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

Note. Foo1, Foo2, Foo3 'will be returned as a single column, not three columns, and GROUP_CONCAT has a maximum of 1024 characters in its default view, although it can be increased. Its not recommended if the results can be large.

Answer 3

Note: the answer table probably does not need a QuestionID column.

This will give you the question text, the selection text, and the number of answers to choose from.

 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: if you are not very well versed in SQL, it is best to forget about the “just one row” part, if only the number of choices per question is not only known, but constant, and even then, it is not easy. This is really a display problem, so deal with it when you display results.

Answer 4

 SELECT questions.question_id, questions.question, choices.choice_id, choices.question_id FROM questions, choices WHERE questions.question_id = choices.question_id AND questions.question_id = "Something" 

Should work, I think.