CODEWITHSUNDEEP
mysql
Joel Kinzel
Joel Kinzel

Reputation: 970

Using column name as table in MySQL query

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

Upvotes: 0

Views: 112

Answers (1)

Joop Eggen
Joop Eggen

Reputation: 109532

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

Upvotes: 1

Related Questions