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 * 
            IFNULL( mfc.hide, ct.hide) as hide, 
            IFNULL(mfc.required, ct.required) as required, 
        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 
    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!

  • 1
    can you explain more, like which columns and of which tables? Dec 28 '15 at 8:11
  • Information schema are not base tables but views. You can still join them though, just like any other table or view. Dec 28 '15 at 9:12
  • I would suggest that having a column contain a "column name" is bad schema design. Please describe what you need to do, not the attempted solution.
    – Rick James
    Dec 28 '15 at 17:30
  • SELECT * FROM ( SELECT ... ) WHERE ... --> SELECT ... HAVING ...
    – Rick James
    Dec 28 '15 at 17:32

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.

  • Hey Anan thanks for the answer. Unfortunately, i am using InnoDB. Cheers! Dec 28 '15 at 9:46

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.