Mysql UNION with dynamic query -

Mysql UNION with dynamic query -

i have next problem. inherited software uses database prefix every customer. tables have same construction , columns. info migration new version want union these tables , set client foreign key instead , rid of subtables. i'm looking way create view task because want remain backwards compatible now. found dynamic query seems want can't execute on mysql server. assume written sql server.

the table name construction (about 80 client tables): customer1_faxe customer2_faxe customer3_faxe customer4_faxe ...

how approach problem?

declare @selectclause varchar(100) = 'select *' ,@query varchar(1000) = '' select @query = @query + @selectclause + ' ' + table_name + ' union ' information_schema.tables table_name '%_faxe' select @query = left(@query, len(@query) - len(' union ')) exec (@query)

this query using sql server syntax. need this:

declare @selectclause varchar(8000); declare @query varchar(8000); set @selectclause = 'select *'; select @query := group_concat(@selectclause, ' ', table_name separator ' union ') information_schema.tables table_name '%_faxe'; prepare stmt @query; execute stmt;

note group_concat() separator simplifies logic.

mysql dynamic union