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 | +--------------+---------------+---------------+-----------------+

Improve this question
4
  • 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, 2019 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, 2019 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, 2019 at 5:17
  • Welcome to the painful coding behind Entity-Attribute-Value schema. See the tag I added.
    – Rick James
    Apr 24, 2019 at 4:32

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.