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. — Aaron Miller
– Aaron Miller, Commented Jul 23, 2013 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
– Joel Kinzel, Commented Jul 23, 2013 at 16:27
@AaronMiller I tried something similar, MySQL complains that there is more than a single value in the variable — Joel Kinzel
– Joel Kinzel, Commented Jul 23, 2013 at 16:32

Joop Eggen · Accepted Answer · 2013-07-23 16:05:21Z

1

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

answered Jul 23, 2013 at 16:05

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Kinzel Over a year ago

Not the cleanest method but it works in my situation. Much appreciated!

Collectives™ on Stack Overflow

Using column name as table in MySQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related