Return fieldname from another table based on ID

Question

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

This is horrible table design. Instead of putting the field IDs in the column names, you should put them in the data, so you can do an ordinary join. The way you've done it, you have to write dynamic SQL using `PREPARE`. — Barmar, Aug 19 '15 at 18:46
If this was not mySQL you may be able to use `unpivot` and then join normally the data. However, as it is mySQL there are only two ways I can think of... 1) dynamic SQL as @Barmar suggests or 2) HARD code a union all to generate the unpivot manually as http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table does. Then you could join as normally. They both have pros/cons depending on context. — xQbert, Aug 19 '15 at 18:48
@Barmar I'm with you on the horrible design but I have no control over the design. It is a legacy system that I have to write some reports for and looking at two different tables in query browser is a pain so i thought i would ask — MatthewD, Aug 19 '15 at 18:49

score 1 · Accepted Answer · answered Aug 19 '15 at 18:53

1

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;

answered Aug 19 '15 at 18:53

Barmar

741,623
53
500
612

Thank you. I'll look into PREPARE. – MatthewD Aug 19 '15 at 18:59
1

Be aware that GROUP_CONCAT has a max length determined by settings (It think it can be set per connection if you can't change the server settings). – Uueerdo Aug 19 '15 at 19:09
1

The default max length is 1024. With field IDs and names like the example he gave, that will allow for around 40 fields. Hopefully that's enough. – Barmar Aug 19 '15 at 19:14

Return fieldname from another table based on ID

1 Answers1