Complex query with many tables, is this possible in one query?

Question

I have troubles with the following database design (simplified for the example). It allows people to create custom forms, quite similar to google spreadsheat forms. The form generation script is done, including saving the answers to the database. I have trouble displaying the user input.

Table forms (each form is a row in this table)

form_id | form_name
1         Contact Form
2         BBQ sign up Form

Table questions (questions in the form)

question_id | form_id | question_name      | sorting_order
1             1         Full Name            1
2             1         E-mail address       2
3             1         Subject              3
4             1         Message              4
5             2         Your name            1
6             2         Are you vegetarian?  2
7             2         Beer or wine?        3

table completed_forms (to group answers together)

complete_id | form_id | timestamp
1            1          1339496914
2            1          1148691493
3            2          1256235334

table answers (answers from users to specific questions)

answer_id | complete_id | question_id | answer
1           1             1             Barack Obama
2           1             2             president@whitehouse.gov
3           1             3             Test message
4           1             4             This is a test. Regards, Barack.
5           2             1             Thomas something
6           2             2             thomas@email.com
7           2             3             Another message
8           2             4             Hey, it's Thomas. This is my message.
9           3             5             Dirk
10          3             6             No, I love meat
11          3             7             Red wine!

What I want to display is an overview of user input, which is quite complex as the names and number of columns differ per form. For example, I want to show the user input from the contact form (form_id: 1) as follows:

id | timestamp | Full Name | E-mail Address | Subject | Message
1    133949...   Barack...   president@w...   Test...   This is a t...
2    114869...   Thomas...   thomas@emai...   Anot...   Hey, it's T...

And from form_id 2 as follows:

id | timestamp | Your name | Are you vegetarian? | Beer or Wine?
3    125623...   Dirk        No, I love meat       Red wine!

Does anyone if and how I can achieve this?

Many thanks for anyone who is willing to take the time to shine a light on this problem!

Do you want two selects, one for each type? – aF. Jun 12 '12 at 11:12 — aF., Jun 12 '12 at 11:12

score 3 · Accepted Answer · edited May 23 '17 at 12:03

Basically you are trying to write a PIVOT but MySQL does not have a PIVOT function per se, so you will have to write the query a little different. For a static version of this query where you will code in the values of the questions you can use the following:

Form 1 (See SQL Fiddle with Demo):

select cf.complete_id
  , cf.ts
  , Min(CASE WHEN q.question_name = 'Full Name' THEN a.answer END) as 'Full Name'
  , Min(CASE WHEN q.question_name = 'Email Address' THEN a.answer END) as 'Email Address'
  , Min(CASE WHEN q.question_name = 'Subject' THEN a.answer END) as 'Subject'
  , Min(CASE WHEN q.question_name = 'Message' THEN a.answer END) as 'Message'
from completed_forms cf
inner join questions q
  on cf.form_id = q.form_id
inner join answers a
  on cf.complete_id = a.complete_id
  and q.question_id = a.question_id
where cf.form_id = 1
group by cf.complete_id

Form 2 (See SQL Fiddle with Demo):

select cf.complete_id
  , cf.ts
  , Min(CASE WHEN q.question_name = 'Your Name' THEN a.answer END) as 'Your Name'
  , Min(CASE WHEN q.question_name = 'Are you vegetarian?' THEN a.answer END) as 'Are you vegetarian?'
  , Min(CASE WHEN q.question_name = 'Beer or Wine' THEN a.answer END) as 'Beer or Wine'
from completed_forms cf
inner join questions q
  on cf.form_id = q.form_id
inner join answers a
  on cf.complete_id = a.complete_id
  and q.question_id = a.question_id
where cf.form_id = 2
group by cf.complete_id

Now since your questions are going to change for each form, then you should look at automating the Pivot. I suggest reviewing the article located here:

http://www.artfulsoftware.com/infotree/queries.php#523

It contains examples of how to use a pivot in MySql. There are also other answers on StackOverflow that should be able to help with writing this dynamically:

mysql query to dynamically convert row data to columns

MySQL pivot table with dynamic headers based on single column data

score 1 · Answer 2 · answered Jun 12 '12 at 11:18

Nice normalized schema!

Doing this in pure SQL is very hard, because you need to create queries that have column-name aliases that come from rows in your questions.question_name.

If you were doing it in pure SQL, you'd be writing SQL statements that would somehow write SQL statements for each distinct value of form_id. That leads to some unmaintainable monkey business in your SQL code. It would completely blow away the work you've done to be able to handle multiple forms easily in terms of your long term costs.

This is far easier to do in PHP, PERL, Java or some other language that does a decent job with manipulating lists of strings.

PostgreSQL and Oracle let you write stored procedures in languages like Java and/or PERL. That might be the way to go if you must have this code resident in your DBMS.

Complex query with many tables, is this possible in one query?

2 Answers2