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.

  • Somewhat depends if it is just four fields, or does it need to be a dynamically changing list?
    – Paul
    Oct 22, 2018 at 11:11

2 Answers 2

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, ...

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.

  • I see what you are saying. the problem is that I am getting column name dynamically.
    – OAH
    Oct 23, 2018 at 21:47
  • You would pass the column name into the SP as an argument.
    – Rick James
    Oct 24, 2018 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.