0

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!

  • 1
    can you explain more, like which columns and of which tables? – jerichorivera 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. – ypercubeᵀᴹ 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
-1

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.

| improve this answer | |
  • Hey Anan thanks for the answer. Unfortunately, i am using InnoDB. Cheers! – user1844786 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.