column name as variable in sql statement

Question

I have a MySQL database with a table that contains 4 columns, A, B, C, and D. I need to access those fields dynamically i.e., instead of saying Table.col_A , I need to say Table.x where x can have the value of any of the columns A or B or C or D. I am getting x from the join with another table.

Answer 1

SELECT CASE another_table.field
           WHEN 'A' THEN main_table.field_A
           WHEN 'B' THEN main_table.field_B
           WHEN 'C' THEN main_table.field_C
           WHEN 'D' THEN main_table.field_D
           ELSE NULL
       END, ...

Answer 2

In a Stored Procedure, you could construct the query with CONCAT, PREPARE and EXECUTE it.

But... The error you are getting may be a wake-up call. Why do you need multiple tables instead of a single table? By moving to a single table you would probably need to add a column to distinguish which subset you are interested in. Also adjust the PRIMARY KEY and other indexes as needed.