0

I have three tables in my MySQL database - company, extra_fields and extra_fields_info:

mysql> describe company;
+-----------------------------+------------------+------+-----+---------+-------+
| Field                       | Type             | Null | Key | Default | Extra |
+-----------------------------+------------------+------+-----+---------+-------+
| company_id                  | varchar(64)      | NO   | PRI | NULL    |       |
| company_name                | varchar(80)      | YES  |     | NULL    |       |
| other_stuff                 | varchar(1)       | YES  |     | n       |       |
+-----------------------------+------------------+------+-----+---------+-------+

mysql> describe extra_fields;
+-----------------------------+------------------+------+-----+---------+-------+
| Field                       | Type             | Null | Key | Default | Extra |
+-----------------------------+------------------+------+-----+---------+-------+
| extra_field_id              | varchar(64)      | NO   | PRI | NULL    |       |
| field_name                  | varchar(80)      | YES  |     | NULL    |       |
| field_required              | varchar(1)       | YES  |     | n       |       |
| field_comment               | varchar(255)     | YES  |     | NULL    |       |
+-----------------------------+------------------+------+-----+---------+-------+


mysql> describe extra_fields_info;
+--------------------------+-------------+------+-----+---------+-------+
| Field                    | Type        | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+---------+-------+
| extra_info_id            | varchar(64) | NO   | PRI | NULL    |       |
| company_id               | varchar(64) | YES  | MUL | NULL    |       |
| extra_field_id           | varchar(64) | YES  |     | NULL    |       |
| field_value              | text        | YES  |     | NULL    |       |
+--------------------------+-------------+------+-----+---------+-------+

Here's an example of some of the data from these fields:

mysql> select efi.extra_field_id, ef.field_name, efi.field_value, efi.company_id from extra_fields_info left join extra_fields ef on efi.extra_field_id = ef.extra_field_id;
+----------------+-------------------+--------------------------------+------------+
| extra_field_id | field_name        | field_value                    | company_id |
+----------------+-------------------+--------------------------------+------------+
| 1              | is_affiliate_user | y                              | 1          |
| 2              | contact_email     | email@gmail.com                | 1          |
| 3              | default_currency  | MYR                            | 1          |
| 4              | vertical          | 33                             | 1          |
| 5              | contact_locale    | en                             | 1          |
| 6              | operating_country | MY                             | 1          |
| 7              | company_name      | SomeCompanyName1               | 1          |
| 1              | is_affiliate_user | y                              | 2          |
| 2              | contact_email     | email2@gmail.com               | 2          |
| 3              | default_currency  | EUR                            | 2          |
| 4              | vertical          | 3                              | 2          |
| 5              | contact_locale    | en                             | 2          |
| 7              | contact_name      | ContactName                    | 2          |
| 6              | operating_country | FR                             | 2          |
+----------------+-------------------+--------------------------------+------------+

What I'd like to do is use some of the rows in the above output as fields in a SELECT. I'd like to visualise my data like this:

+------------+------------------+------------------+-------------------+----------+
| company_id | company_name     | contact_email    | operating_country | vertical |
+------------+------------------+------------------+-------------------+----------+
| 2          | SomeCompanyName1 | email2@gmail.com | EUR               | 3        |
| 1          |                  | email@gmail.com  | MY                | 33       |
+------------+------------------+------------------+-------------------+----------+

As you can see, I'm using some of the extra_fields as field names. I only need to select a few of these fields as depicted above - I don't need all of them.

I'm not even sure where to start with this, so any guidance would be appreciated.

  • You are re-inventing the Entity-Attribute-Value schema pattern. Condolences. – Rick James Oct 15 at 2:12
0

If extra fields types list is static, then

SELECT company_id,
       MAX(CASE WHEN extra_field_id = 2 THEN field_value END) AS contact_email,
       MAX(CASE WHEN extra_field_id = 3 THEN field_value END) AS default_currency,
       ...
FROM joined_tables
GROUP BY company_id

If not, then dynamic SQL only.

  • Works perfectly. Thank you! – dunc Oct 8 at 9:54

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.