0
It is possible to use CONCAT to form the name of the table to be consulted?
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 that. I just did a short example to simplify the question.
0
I researched a lot on the subject and found several solutions, however, none of the solutions was suitable for this specific case.
In general, one solution is to use the SQL Prepared statements. It is not the PDO statement or other library, but directly in the SQL syntax.
The problem is that in my case, part of the table name comes from the same query in column "t1.col".
Below, an example table name mount 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 picked up this example at random. Source: https://stackoverflow.com/questions/10480429/how-to-select-from-dynamic-table-name
In my case it does not work because I need to consult a column of the main query.
Since the query that concatenates is interpreted earlier by "Prepare stmt", it returns non-existent column error "t1.col".
The idea behind all this was to reduce executions of SQL queries within a repetition loop. In the final conclusion there was no escape. The solution was to assemble multiple queries within a repeat loop.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.