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

Question

Hello, 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

I'm going to assume that you're trying to get "[CATEGORISATIONS.nom_table]" to resolve to a table name.

In that case you can try to use a subquery there that will resolve to only 1 table name (So a resultset with 1 row and 1 column). I'm not sure if MySQL will accept a string name for a table there, but its worth a try.

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.

Answer 3

Maybe the guy who did this was aware that it's really not a good design practice... but sometimes you have to make crappy hack to do what you want to do...

From what I know about the subject, I don't think you can achieve that with only a SELECT query.

Maybe with a stored proc...

Answer 4

I have tried to use mysql table values returned from a subquery as field names before and wasn't successful. I could never get mysql to accept a recordset value as a field name. I had to use php to accomplish this task, unfortunately.

Answer 5

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;