Last post Feb 18, 2021 08:39 AM by PatriceSc
Feb 18, 2021 05:08 AM|Edward Sheriff Curtis|LINK
I use this SQL query for find, in database MySql version 8.0.17, the tables whose name corresponds to the condition indicated in the clause LIKE
MySql version 8.0.17
WHERE table_name LIKE 't#_contents#_s3%#_1#_2021' ESCAPE '#';
| TABLE_NAME |
| t_contents_s300_1_2021 |
| t_contents_s34d_1_2021 |
| t_contents_s34g_1_2021 |
| t_contents_s3sv_1_2021 |
4 rows in set (0.30 sec)
My question is
How to do go about for union all these tables returned by the SQL query ?
Normally I would do this mode, but I don't know first all the names of the tables and the number of the tables returned by the SQL query... the names and the number of the tables it could be variable...
I'm not the administrator of this database. This schema is a remote hosting where I can only read..
`t_contents_s300_1_2021` UNION ALL
`t_contents_s34d_1_2021` UNION ALL
`t_contents_s34g_1_2021` UNION ALL
Thanks in advance for any help.
Feb 18, 2021 08:39 AM|PatriceSc|LINK
You first query could generate a SELECT statement for each table ie SELECT CONCAT('SELECT .. FROM ',table_name) etc... and then use StingJ.Join to concat those statements with "UNION ALL" in between.
Or if you want to do all on the MySQL side you could sue GROUP_CONCAT to do the same thing and then use EXECUTE to run the resulting statement.
I understand you don't have control on the db but another option could be to expose that through a view '(that you'll need to update each year) unless tables are created in advance.
You have also partioned tables allowing to handle this kind of schema deisgn