Complex query with many tables, is this possible in one query?
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 email@example.com 3 1 3 Test message 4 1 4 This is a test. Regards, Barack. 5 2 1 Thomas something 6 2 2 firstname.lastname@example.org 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!
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:
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:
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.