JOIN query... including another table name

[perpetualshaun]

I would think there's a way to do this... I'm trying to write a query with a few joins and all's working according to plan until the end.

Here's my query:

SELECT r . * , c . * , q.questionnaire_abbr, s.response_completed_on
FROM rr_references r
LEFT JOIN rr_candidates c ON c.candidate_id = r.reference_candidate_id
LEFT JOIN admin a ON a.id = c.candidate_client_id
LEFT JOIN questionnaires q ON q.questionnaire_id = a.questionnaire_id
LEFT JOIN questionnaire_abbr AS s ON s.response_pcode = r.reference_password
WHERE reference_deleted =0
AND reference_candidate_id = '105'
AND reference_client_id = '2959'
ORDER BY reference_first_name ASC
LIMIT 0, 50 

The problem here is that the questionnaire_abbr variable that I'm getting in this query is also the name of another database table out there. So I want that last LEFT JOIN to somehow translate into the full value for what q.questionnaire_abbr is.

Is this possible?

Please advise,
Shaun

[johanafm]

I've never used CASE like this, but perhaps this would work

LEFT JOIN CASE questionnaire_abbr WHEN 'table_a' THEN table_a AS t WHEN 'table_b' THEN table_b AS t ELSE table_c AS t END AS s ON s.response_pcode = r.reference_password

[Bjom]

You have the same kind of information spread over several tables? Why not have them in one table with proper identifiers. That should make querying and maintenance much easier.