Mysql – How to join information schema to the table’s data

information-schemajoin;MySQL

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!

Best Answer

That depends upon the type of DB you actually use.

In NOSQL like MongoDB support Dynamic schemas to join the table using column name which results in embedded documents.

Related Question