Need help rewriting view to avoid using subqueries in MySQL 5.1 db

Question

I am working on creating a view in MySQL for an application that tracks form submissions. There is one particular table that I cannot figure out how to write a query for. It has foreign_keys relationships with a submissions table and a table which keeps track of fields in all the pdfs used by the application. A basic overview of the table structure and application is as follows:

1. The frm_form_attribute_data (the table I am struggling with) table stores information relating to a form submission and the data from a specific field on that form.
2. Each entry in the frm_form_attribute_data_table has a unique id, the id of a submission, the id of a form field from a pdf, and the data that was filled in.
   1. An example of the attribute id: if a pdf has a "name" field, then it might have an attribute_id of 1 associated with it that will always be the "name" field on that specific pdf.
3. So for every 1 form submission, there will be multiple entries in the data table, since for a given form there might be ~20-30 different, distinct fields
4. The fields are differentiated with an attribute_id

The issue I am having is that I need to create a view which aggregates data based on each submission and am struggling to write queries that don't use subqueries to achieve this. The version of MySQL (5.1) that I am working with does not allow subqueries when creating views. Each row in the view should correspond to 1 unique submission_id and contain all the attribute_ids and their data as columns. So an entry for the first submission will have the submission_id, and attributes 1 through x (where x is there number of fields in that specific pdf/form) as column data. Instead of showing the attribute_id, the columns will just use the common name for that attribute(email, name, etc). I can successfully build a query which does this with the following:



SELECT submission_id, attribute_id, is_reporting, email
FROM (
    SELECT form_submission_id, attribute_id, response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382
) isReporting
INNER JOIN (
    SELECT form_submission_id, attribute_id, response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385
) email ON isReporting.form_submission_id = email.form_submission_id

Each INNER JOIN grabs the data for a specific attribute using a subquery and appends it as a column in the “view”. However, the subqueries in the FROM and INNER JOIN are not allowed in versions of MySQL < 5.7. I saw as an alternative that I can create a view for each sub query, but some of the forms have 30+ fields and I would have to create a separate view for each one.

Is there a way to structure the query without using subqueries? Or will I need to create all the smaller views corresponding to a specific attribute_id.

Please bear in mind that I am unable to change the version of MySQL we are running :( There are migrations in progress but that is outside the scope for my role and team.

EXAMPLES of tables:

Table: frm_form_attribute_data

id	submission_id	attribute_id	response
1	20	1	yes
2	20	2	[email protected]
3	21	1	no
4	21	2	NULL

Table: Desired View

id	submission_id	isReporting	email
1	20	yes	[email protected]
2	21	no	NULL

Answer 1

I hope the next code is clear enough

SELECT q.submission_id
     , q.attribute_id
     , q.response AS is_reporting
     , w.response AS email
  FROM frm_form_attribute_data AS q
  JOIN frm_form_attribute_data AS w 
    ON q.form_submission_id = w.form_submission_id 
 WHERE q.attribute_id = 382
   AND w.attribute_id = 385
 ;

INNER JOIN .. ON is equivalent to JOIN .. ON so I replaced it.

Answer 2

If each attribute is unique for each submission id then you can use conditional aggregation to pivot the table (and does not require any joins):

SELECT submission_id,
       MAX(CASE WHEN attribute_id = 382 THEN response END) AS is_reporting,
       MAX(CASE WHEN attribute_id = 385 THEN response END) AS email
FROM   frm_form_attribute_data
WHERE  attribute_id IN (382, 385)
GROUP BY submission_id;

Which, for the sample data:

CREATE TABLE frm_form_attribute_data (
  id            INT PRIMARY KEY,
  submission_id INT,
  attribute_id  INT,
  response      VARCHAR(50),
  UNIQUE (submission_id, attribute_id)
);

INSERT INTO frm_form_attribute_data (id, submission_id, attribute_id, response)
  VALUES (1, 20, 382, 'yes'),
         (2, 20, 385, '[email protected]'),
         (3, 21, 382, 'no'),
         (4, 21, 385, NULL);

Outputs:

submission_id	is_reporting	email
20	yes	[email protected]
21	no	null

fiddle

Answer 3

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

Edit: I want to highlight both @MT0 and @Kondybas solutions as they both work, but just did not run as fast as the solution I found on my specific data and architecture (but may work for others with similar issues).

MT0's solution is great because it avoids JOINS entirely, but the aggregate functions ran slow on the queries I made using the conditional aggregate approach. The View created with conditional aggregates took ~15s to load. I am running ~30 functions every time the View is loaded in addition to other components of the query which join with other tables.

Kondybas' solution with the self JOINs ran on average 2.3s after some testing which is really good! I do think the query is easier to read and understand compared to the solution I found.

My final solution averaged ~450ms for the query. I think highlighting different viable solutions is important, even though I did not select them as the final answer.