Mysql – Using rows from one table as fields in SELECT

eavMySQL

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.

Best Answer

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.

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Mysql – “Lost connection to MySQL server during query” error

I am running MySQL 5.7.26 on Windows and when doing just a simple query from the MySQL command line client I get that same error you describing. I found out it only occurs when the query length is exact 64 characters! Just by adding a space fixed the problem for me.
It doesn't matter if it's a select or insert/delete/update query.
I have run 66.000 queries on my server and only the queries with exact 64 characters failed.
I checked the database for corruption. But I could also reproduce same problem when making a new database and table on that server.
I could only reproduce this problem on that production server. I created a new server with same version with exact same settings as a VMWare machine but could not recreate. Used the same MySQL client.

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

Mysql – “Lost connection to MySQL server during query” error

Related Question