I have a quiz/survey program and I am working on putting together some more advanced analysis of the results, beyond merely being able to see the answers and how many people answered what. One of the things I want to do is to be able to see how people answered a specific question, and then also see how those same people answered another question, and display that information in one table.
A response is stored in different tables, depending on the question type. (There is a "question_type" table that defines the different question types and what table their answers are placed in.) Answers to a question with a single answer are stored in "response_single," answers to a question with a long text answer are stored in "response_text," and so on for 4 or 5 different question types. The questions themselves are stored in a "question" table, and the choices for each question are stored in a "question_choice" table.
That would basically show me that of the people who answered "dog" for the first question, 7 of them answered "10" for the second question, 5 answered "20," etc, etc.
Here is my table structure (with the important and related data).
survey
- id (a unique id for each survey)
question_type
- id (a unique id for each different question type)
- response_table (defines what table answers are stored in, i.e.- response_single, response_text, etc.)
question
- id (a unique id for each question)
- survey_id (relates to survey.id field)
- type_id (relates to question_type.id field)
question_choice
- id (a unique id for each choice)
- question_id (relates to question.id field)
- content (text of the choice)
response
- id (a unique id for each complete response)
- survey_id (relates to survey.id field)
response_single
- response_id (relates to response.id field)
- question_id (relates to question.id field)
- choice_id (relates to question_choice.id field)
response_text
- response_id (relates to response.id field)
- question_id (relates to question.id field)
- choice_id (relates to question_choice.id field)
- response (whatever text answer was given)
response_rank
- response_id (relates to response.id field)
- question_id (relates to question.id field)
- choice_id (relates to question_choice.id field)
- rank (integer of rank given)
Let's say question 1 (question.id:1) is: What is your favorite pet? Question 5 (question.id:5) is: How many times per week do you feed your pet? I want to know how the answers to question 1 relate to the answers given for question 5. So I want my output table to look something like this:
| 10 | 20 | 30 | 40 |
dog | 7 | 5 | 6 | 8 |
cat | 8 | 7 | 3 | 9 |
fish | 2 | 4 | 7 | 5 |
My question is, how can I do that knowing only the two question_id's that I want to compare? I hope that makes sense, and I hope some kind and intelligent person has the patience to have read this far and provide me with a solution or at least some hints. I know it's likely going to require performing an inner join(s) of some sort, but I can't get the query correct. Many, many thanks in advance.
