Clever Geek Handbook

How do I join a group of tables in a database with a similar name using a SQL query?

For example, I have a database called db and it has 100 tables called ta_1

... ta_100

respectively. Each table has two columns id(int)

and val(int)

.

  • If I want to get the total val, what should I do?
  • If I want to get a table as a result with two columns: tablename (String) and mean (float), what should I do?
+3


source to share


1 answer


It goes without saying that you shouldn't have set up your database this way. There are a number of solutions, ranging from simple but mundane to complex but useful ones.

At some point, you need to create a list of tables and prepare a statement that concatenates them together. Copying and past using the database view would be a good start:

create view GlobalView (
    tabname varchar(10),
    id int,
    val int)
as
   select 'ta_1', id, val from ta_1
UNION ALL
   select 'ta_2', id, val from ta_2
UNION ALL
   select 'ta_3', id, val from ta_3
UNION ALL
   .....
UNION ALL
   select 'ta_100', id, val from ta_100


Then you can just run select:

select avg(1.0 * val) as mean
from GlobalView

An alternative would be to create a dynamic query that you can call either from an external program or using "dynamic SQL"

+2


source






More articles:


All Articles