0

I'm good with basic types of queries but this has me stumped. I have the table schema listed below, I need display the results in a table, with each row displaying the available results for a given date. I've also drawn out the required table layout below. I hope this is clear enough. Is there a query I could use to fetch these results at once? Currently, I'm queries each date and then stitching the results together which is inefficient.

Fields Table Schema

+--------------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+----------------+------+-----+---------+-------+ | id | varbinary(255) | NO | PRI | | | | value | varchar(255) | YES | | NULL | | | form_submission_id | varbinary(255) | YES | MUL | NULL | | | form_field_id | varbinary(255) | YES | MUL | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | | deleted_at | timestamp | YES | MUL | NULL | | +--------------------+----------------+------+-----+---------+-------+

Required Query Results

+--------------+---------------+---------------+-----------------+ | created_at | form_field_id | form_field_id | form_field_id 3 | +--------------+---------------+---------------+-----------------+ | Apr 14, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 13, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 12, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 11, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+ | Apr 10, 2019 | value | value | value | +--------------+---------------+---------------+-----------------+

  • 1
    Are the form_field_id values known ahead of the query? Where there is more than onve value per day, which do you want? Looking pivot tables. I assume you know how to aggregate on created_at day on display its date? What MySQL version? – danblack Apr 15 at 5:13
  • @danblack I will have an array of form_field_id ahead of time. Never more than value per day. Mysql V8.0.15 – Shane Da Silva Apr 15 at 5:14
  • 1
    It is referred to as "pivot". MySQL have no this type of query. I'd recommend to perform this transformation on the client side using its reporting subsystem. Fields Table Schema Replace it with table's CREATE TABLE. Required Query Results Replace it with sample data (INSERT INTO) and the result on THAT data. Alternatively to evereything above - create a fiddle. – Akina Apr 15 at 5:17
  • Welcome to the painful coding behind Entity-Attribute-Value schema. See the tag I added. – Rick James Apr 24 at 4:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.