Require assistance to write Query joining 3 tables

Question

I have a table called form_submission_entries which contains data like this:

id	submission_id	field_id	value
1	1	1	John
2	1	2	Doe
3	1	3	27
4	2	1	Jane
5	2	2	Doe
6	2	3	23

The field_id corresponds to a form_fields table which looks like this:

id	name	title
1	first_name	First Name
2	last_name	Last Name
3	age	Age

And of course a form_submissions table which looks like this:

id	form_id	submitted_at
1	1	2021-11-05 08:16:27
2	1	2021-11-05 08:28:11

I would like to run a single query that will allow me to create a CSV file that looks like this:

submission_id	first_name	last_name	age	submitted_at
1	John	Doe	27	2021-11-05 08:16:27
2	Jane	Doe	23	2021-11-05 08:28:11

I am able to achieve this by creating multiple queries. First by getting a list of ids from the form_submissions. Then using that list of ids to create multiple queries to fetch data from the form_submission_entries using the submission_id. But it looks nasty. I was hoping someone somewhere would have a much better approach. Thanks so much!

Answer 1

If I understand your schema and goal, there are a couple of different ways you can achieve this. This is one way you can get the output you're looking for:

SELECT 
    FS.id AS submission_id, 
    FirstName.value AS first_name,
    LastName.value AS last_name,
    Age.value AS age,
    FS.submitted_at
FROM form_submissions FS
LEFT JOIN form_submission_entries FirstName
    ON FS.id = FirstName.submission_id
    AND FirstName.field_id = 1
LEFT JOIN form_submission_entries LastName
    ON FS.id = LastName.submission_id
    AND LastName.field_id = 2
LEFT JOIN form_submission_entries Age
    ON FS.id = Age.submission_id
    AND Age.field_id = 3

The query should be fairly self-explanatory but essentially it starts with the parent form_submissions table and joins to the child form_submission_entries three times, each time only on a specific entry. I'm using LEFT JOIN (which is an outer join) just in case any of your entries are not required. If they are all required and all three will always exist for a given submission then you can switch the LEFT JOINs to INNER JOINs instead.

Finally to turn the above SQL result set into a CSV output, you just have to concatenate the columns in the SELECT list with commas, and union in your headers, like so:

SELECT 
    CONCAT
    (
        'submission_id', ',',
        'first_name', ',',
        'last_name', ',',
        'age', ',',
        'submitted_at'
    )

UNION ALL

SELECT 
    CONCAT
    (
        FS.id, ',',
        FirstName.value, ',',
        LastName.value, ',',
        Age.value, ',',
        FS.submitted_at
    )
FROM form_submissions FS
LEFT JOIN form_submission_entries FirstName
    ON FS.id = FirstName.submission_id
    AND FirstName.field_id = 1
LEFT JOIN form_submission_entries LastName
    ON FS.id = LastName.submission_id
    AND LastName.field_id = 2
LEFT JOIN form_submission_entries Age
    ON FS.id = Age.submission_id
    AND Age.field_id = 3