FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE forms (
  id INT AUTO_INCREMENT KEY,
  title VARCHAR(200)
);

CREATE TABLE fields (
  id INT AUTO_INCREMENT KEY,
  label VARCHAR(200)
);

CREATE TABLE form_fields (
  id INT AUTO_INCREMENT KEY,
  form_id INT,
  field_id INT
);

CREATE TABLE submissions (
  id INT AUTO_INCREMENT KEY,
  form_id INT
);

CREATE TABLE submission_fields (
  id INT AUTO_INCREMENT KEY,
  submission_id INT,
  field_id INT,
  field_value VARCHAR(255)
);

INSERT INTO forms(title) VALUES ('Form 1');
INSERT INTO fields(label) VALUES ('First Name');
INSERT INTO fields(label) VALUES ('Last Name');
INSERT INTO fields(label) VALUES ('Email Address');
INSERT INTO form_fields(form_id, field_id) VALUES (1, 1);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 2);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 3);
INSERT INTO submissions(form_id) VALUES (1);
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 1, 'Joe');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 2, 'Smith');

-- QUERY database
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,
    GROUP_CONCAT(COALESCE(submission_fields.field_id, '') SEPARATOR '|') AS submission_field_ids,
    GROUP_CONCAT(COALESCE(submission_fields.field_value, '') SEPARATOR '|') AS submission_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
    submission_fields
ON
    submission_fields.submission_id = submissions.id
LEFT OUTER JOIN
    fields
ON (
    fields.id = submission_fields.field_id OR
    fields.id = form_fields.field_id
)
WHERE
    submissions.id = 1
GROUP BY
	submissions.id;

submission_id	form_id	form_title	form_fields_field_ids	field_labels	submission_field_ids	submission_field_values
1	1	Form 1	1\|2\|3\|1\|2\|1\|2\|3\|3\|3	First Name\|First Name\|First Name\|First Name\|Last Name\|Last Name\|Last Name\|Last Name\|Email Address\|Email Address	1\|1\|1\|2\|1\|2\|2\|2\|1\|2	Joe\|Joe\|Joe\|Smith\|Joe\|Smith\|Smith\|Smith\|Joe\|Smith