0

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!

2
  • Could you post the statements that you already have? That would help the community know where your problems might be.
    – John K. N.
    Nov 22, 2021 at 7:59
  • A better approach would be to redesign form_submission_entries so that it corresponds to the layout of the form. It would make your query a lot simpler, and the same goes for any application using that data. Nov 22, 2021 at 9:30

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.