Search code examples
mysqlmariadb

Joining multiple MySQL tables where some data in one may not exist

I'm bringing 5 tables together in a rather complicated query for a form builder. This builder allows site admins to create forms, create fields and add those fields to any form, and then display the form on the front-end for users (after users submit the form, they can then go in and edit the values at any time; think something similar to how profiles work).

The problem I'm facing is that, since admins can edit the forms to add new fields to them even after there have been submissions, when I pull in the new field for someone editing their existing submission, it does get the new field but it has the same value as the field that was right before it in the order they come in.

Here is an SQL Fiddle demonstrating the issue: https://sqlfiddle.com/mariadb/online-compiler?id=cf46ab33-382b-4d7f-8958-c9dd3bf19855

Solution

  • After you posted your fiddle I got a better understanding of the situation. I created a new query for you, this time using subquery's to get the job done:

    SELECT
    submissions.id AS submission_id,
    submissions.form_id AS form_id,
    forms.title AS form_title,
    GROUP_CONCAT(COALESCE(form_fields.field_id, '') SEPARATOR '|') AS form_fields_field_ids,
    GROUP_CONCAT(COALESCE(fields.label, '') SEPARATOR '|') AS field_labels,
    (SELECT GROUP_CONCAT(COALESCE(submission_fields.field_id, '') SEPARATOR '|') 
     FROM submission_fields 
     WHERE submission_fields.submission_id = submissions.id) AS field_ids,
    (SELECT GROUP_CONCAT(COALESCE(submission_fields.field_value, '') SEPARATOR '|') 
     FROM submission_fields 
     WHERE submission_fields.submission_id = submissions.id) AS field_values
FROM
    submissions
LEFT OUTER JOIN
    forms
ON
    forms.id = submissions.form_id
LEFT OUTER JOIN
    form_fields
ON
    form_fields.form_id = submissions.form_id
LEFT OUTER JOIN
    fields
ON
    fields.id = form_fields.field_id
WHERE
    submissions.id = 1
GROUP BY
    submissions.id;

    Within the fiddle my result is as follows: enter image description here

    I hope this is what you need