MYSQL join a table which its name is stored as a field value on another table

Question

I am trying to build a dynamic query in MYSQL to join a table which its name is stored as a field value on another table like this :

SELECT * FROM CATEGORIES 
INNER JOIN CATEGORISATIONS ON CATEGORISATIONS.id = CATEGORIES.fk_categorisation
INNER JOIN [CATEGORISATIONS.nom_table] LV_REGIONS ON LV_REGIONS.id = CATEGORIES.valeur

Any answer?!

Answer 1

You should build dynamic SQL http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html

like:

select CATEGORISATIONS.nom_table into @tmp FROM ...;
PREPARE stmt1 FROM "SELECT * FROM ... INNER JOIN @tmp ..."; 
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Answer 2

My first recommendation is to find whoever designed that schema and give them a good book on relational database design.

From there, you can head one of two directions: The first is to undo the mess you have in the schema by loading of the separate tables named by CATEGORIZATIONS.nom_table into a single REGIONS table that you can query against directly.

Alternatively, you'll need to break that query into multiple pieces, using the result of the first INNER JOIN to construct a UNION query to do the second INNER JOIN.