I have 4 different tables that are used to gather information from the user, and I'm trying to write a query to simplify the output so that the responses can all be viewed as a single row. The tables are setup like so:
Surveys
+-----+-----------+
| sID | sTitle |
+-----+-----------+
| 1 | Survey 1 |
| 2 | Survey 2 |
+-----+-----------+
Entries
+-----+-----+
| eID | sID |
+-----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+-----+-----+
Questions
+-----+-----+------+------------+
| qID | sID | type | question |
+-----+-----+------+------------+
| 1 | 1 | text | question A |
| 2 | 1 | text | question B |
| 3 | 2 | text | remark A |
| 4 | 2 | text | remark B |
+-----+-----+------+------------+
Entry Responses
+-----+-----+-----+-------------+
| rID | eID | qID | response |
+-----+-----+-----+-------------+
| 1 | 1 | 1 | yes |
| 2 | 1 | 2 | no |
| 3 | 2 | 1 | maybe |
| 4 | 2 | 2 | no |
| 5 | 3 | 1 | foo |
| 6 | 3 | 2 | bar |
| 7 | 4 | 3 | answer |
| 8 | 4 | 4 | unicorns |
+-----+-----+-----+-------------+
etc...
And I would like to have a query that displays them as such:
+-----+----------+-----+------------+------------+
| sID | sTitle | eID | question A | question B |
+-----+----------+-----+------------+------------+
| 1 | Survey 1 | 1 | yes | no |
| 1 | Survey 1 | 2 | maybe | no |
| 1 | Survey 1 | 3 | foo | bar |
+-----+----------+-----+------------+------------+
AND / OR
+-----+----------+-----+------------+------------+
| sID | sTitle | eID | remark A | remark B |
+-----+----------+-----+------------+------------+
| 2 | Survey 2 | 4 | answer | unicorns |
+-----+----------+-----+------------+------------+
Depending on which survey is queried as that would be the limiting factor. Essentially, I would like to get all the people who responded to a survey and their answers to the questions which can be different depending on the survey. Have the questions that were asked per survey be a column header, and the answer's be in the rows.
I'm currently doing some long joins and query's to get each individual response per question, and manually adding it to a row so I'm hoping there's a better way to do it.
