I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible?
I actually found another way to work around it. I am using Innodb. For the structure it's kind of complicated, but this is the query I was working with :
SELECT c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN ( SELECT * FROM ( SELECT ct.field_name, ct.length, ct.description, IFNULL( mfc.hide, ct.hide) as hide, IFNULL(mfc.required, ct.required) as required, ct.example FROM xxxxxx.config_marketplace ct LEFT JOIN xxxxx.merchant_field_config mfc ON mfc.field_id = ct.id AND mfc.industry = 'MP' AND mfc.merchant_id = '1512MP5' ) as t WHERE hide != 'Y' ) b ON LOWER(b.field_name) = c.COLUMN_NAME WHERE c.TABLE_SCHEMA = 'XXXXX_1512MP5' AND c.TABLE_NAME = 'marketplace_custom';
Basically, config_marketplace holds the default configuration of the field names(column name) that will be shown, while merchant_field_config does the configurations of the fields I would like to hide based on each merchant's requirements. Lastly, marketplace_custom would be placed in the individual merchant's db with the column header which I would like to join to the data in that table.
PS. I know it's not a very good explanation.
Just out of curiosity, is this possible? I am working around it, but it will be a really good thing to know for future usage!