Using column name as table in MySQL query

Question

I have a database in which the column in one table is the table name in which I need to look for a corresponding record.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| TABLE questions                                                                                                                                                                                 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Field                   | Type                                                                                                                                                                  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| question_id             | int(11)                                                                                                                                                               |
| question_response_table | enum('question_responses_datetime','question_responses_int','question_responses_float','question_responses_bool','question_responses_text','question_responses_enum') |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Each of the values in question_response_table column is another table which holds a user response (the original DB design called for the responses to be separated out by data type into separate tables).

I have a query which gets me everything I need except for the user's response. But I'd really like to tack that response onto the same query for performance reasons (the DB grows significantly each year). Here is an example of what I'd like to do:

SELECT cr.category_id, cr.category_response_id, r.response 
FROM category_responses AS cr 
JOIN response_key_questions AS rkq ON cr.category_id = rkq.category_id 
JOIN questions AS q ON q.question_id = rkq.question_id
JOIN {q.question_response_table} AS r ON r.category_response_id = cr.category_response_id 
WHERE cr.belongs_to = 4 AND cr.reporting_year_id = 1 AND cr.date_retired IS NULL 
ORDER BY cr.category_id

You want something like `eval()`, which doesn't exist in SQL, and for extremely good reason; the whole point of a schema is that it rigidly defines the relationships which may exist between various pieces of data, and permitting the use of arbitrary strings as column or table names renders that an impossible task. You'd do best to refactor with responses in a single table, and define the relationship in terms of a foreign key, but if you can't do that, then you might find something useful [here](http://stackoverflow.com/questions/13282718/dynamic-conversion-of-string-into-column-name-mysql). — Aaron Miller, Jul 23 '13 at 16:03
@eggyal Unfortunately I don't have a choice in how the DB was designed, it is what it is and there are hundreds of thousands of records thus far — Joel Kinzel, Jul 23 '13 at 16:27
@AaronMiller I tried something similar, MySQL complains that there is more than a single value in the variable — Joel Kinzel, Jul 23 '13 at 16:32

score 1 · Accepted Answer · answered Jul 23 '13 at 16:05

This will become so ugly. Better do a CREATE VIEW where the response column is added to the cr table.

SELECT cr.category_id, cr.category_response_id,
    CASE 
    WHEN q.question_response_table = '...' THEN
        (SELECT response
         FROM ... r
         WHERE r.category_response_id = cr.category_response_id)
    ... 
    ELSE '' 
    END AS response
FROM category_responses AS cr 
JOIN response_key_questions AS rkq ON cr.category_id = rkq.category_id 
JOIN questions AS q ON q.question_id = rkq.question_id
WHERE cr.belongs_to = 4 AND cr.reporting_year_id = 1 AND cr.date_retired IS NULL 
ORDER BY cr.category_id

Not the cleanest method but it works in my situation. Much appreciated! — Joel Kinzel, Jul 23 '13 at 16:45

Using column name as table in MySQL query

1 Answers1