Using the value of a field as column name in a query

Question

I'm trying to figure out a way to only grab the specific field that pertains to an operation step a unit is in. I have one table with units and their operation step, then I have another table that has a column for each step. I want to pull only the column that relates to that unit's current step.

Here is my attempt but I can't seem to find a way to use the value of operation as a reference. My problem is I need the field that matches the value of t1.operation in the subquery rather than the value of t1.operation. Did that make any sense?

SELECT t1.*,
  (SELECT t1.operation
   FROM report_tables.roc_capacity_standards As t2
   WHERE t1.assembly=t2.part_number) As standard
FROM report_tables.resource_transactions As t1
WHERE t1.date BETWEEN '2010-06-01'
                  AND '2010-06-19'
GROUP BY job, employee
ORDER BY operation;

After searching online I found that one person said it is not possible mainly because MySQL would not know what it is querying in order to optimize it based on indexes etc. That was pertaining to dynamic table names though so I wasn't sure it was the same.

That was found here: Reference

Answer 1

I believe your problem lies in the design of your tables and not your query.

If I am correct, your tables look similar to this:

units (unit_id, current_operation, date, ...)
operations (unit_id, operation_1_value, operation_2_value, operation_3_value, ...)

This is poor design as it leads to problems like your having now.

A better design would be

units (unit_id, *other unit specific info* ...)
operations (operation_id, operation_name, *other general operation info*)
unit_operation (unit_id, operation_id, *operation_results, *operation_values, *date_started, *date_ended)