Using rows from one table as fields in SELECT

Question

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 protected]                | 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     | [email protected]               | 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 | [email protected] | EUR               | 3        |
| 1          |                  | [email protected]  | 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.

Answer 1

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.