CODEWITHSUNDEEP
mysqlsql
MatthewD
MatthewD

Reputation: 6761

Return fieldname from another table based on ID

I'll start off by saying that I am aware that this is a bad table design. I have no control over the design. It is a legacy system that I have to write some reports for. Looking at two tables in workbench going back and forth is not ideal so i'm looking for a better way to view the second table.

I have two tables.

First table

select CustomFieldId, FieldName from qcdt_jobs_CustomFields where projectId=181;

Retruns 

CustomFieldID   FieldName  
-------------   ---------  
4202            Invoice Date  
4203            Invoice Number  
Etc.

Second table 

SELECT * FROM qcdt_jobs_customfields_181 where JobID=1;

Returns

JobID  CF4202      CF4203   Etc.  
------ --------    -------  -------  
1      11/18/2014  45140    Value

So the field names in the second table are defined in the first table along with the CustomField Number (CF####) that the column will be named.

My question, is there a query that will return the field names for the second table(from the first table) instead of the CF#### field name? So it would look like this.

JobID  Invoice Date    Invoice Number  Etc.  
------ --------------  --------------  -------  
1      11/18/2014      45140           Value

Upvotes: 0

Views: 174

Answers (1)

Barmar
Barmar

Reputation: 781028

You have to generate dynamic SQL and use PREPARE to execute it.

SELECT GROUP_CONCAT(CONCAT('CF', CustomFieldID, ' AS `', CustomFieldName, '`')) INTO fieldNames
FROM qcdt_jobs_CustomFields
WHERE projectId = 181;

PREPARE stmt FROM CONCAT('SELECT JobID, ', fieldNames, ' Etc. FROM qcdt_jobs_customfields_181 where JobID=1');
EXECUTE stmt;

Upvotes: 1

Related Questions