Is it possible to use CONCAT to form the name of the table to be queried?
Example:
SELECT T1.id, T1.col FROM tbl AS T1
LEFT JOIN CONCAT('tabela_prefixo_', t1.col) AS T2 ON T2.id = T1.id
The actual query is much more complex than this. I just did a short example to simplify the question.
I did a lot of research on the subject and found several solutions, but none of the solutions was suitable for this particular case.
Generally, one solution is to use SQL prepared statements. It is not the prepare statement of the PDO or other library, but directly in the SQL syntax.
The problem is that in my case, some of the table name comes from the same query in the "t1.col" column.
Below, an example table assembly dynamically with Prepare Statement:
SET @b := SELECT CONCAT('changes',year,month) FROM whichchanges;
SET @x := SELECT * FROM @b;
Prepare stmt FROM @b; # Aqui nesse ponto, o primeiro SELECT é interpretado.
Prepare stmt FROM @x;
Execute stmt;
* I took this example randomly. Source: link
In my case it does not work because I need to consult a column of the main query.
As the query that concatenates is interpreted before by the "Prepare stmt", it returns a non-existent column error "t1.col".
The idea behind it all was to reduce execution of SQL queries within a loop of repetition. In the final conclusion had no escape. The solution was to mount multiple queries into a loop.