CODEWITHSUNDEEP
mysqlsqlmariadb
Alexander Velez
Alexander Velez

Reputation: 27

How to join a group of tables with the same suffix?

So I am no MYSQL expert and I really need some help trying to figure this out. I currently have over 60 tables that I wish to join into a single table, none of the data in those tables match each other, so I need the rows of all the tables into a single one. They do have the same schema if that is the correct term, basically the same format. They all end in the same suffix '_dir'.

What I thought that could work was something like this,

Get all tables under the same suffix, For each table in the table list join or insert row into main_table.

I don't know how to do this in mysql or if its even possible. I know I can use,

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_dir%'

to get the list of all the tables, but how can I use this to iterate over every table?

Here is an example of input data:

table 1:

|  NAME  |          INST_NAME              | Drop
| data 1 | 'this is an example instance1   | 1.5 
| data 1 | 'this is an example of instance2| 2.0

table 2:

|   NAME |     INST_NAME                   | DROP
| data 2 | 'this is an example instance1   | 3.0 
| data 2 | 'this is an example of instance2| 4.0

Output table:

|  NAME  |      INST_NAME                  | DROP
| data 1 | 'this is an example instance1   | 1.5 
| data 1 | 'this is an example of instance2| 2.0
| data 2 | 'this is an example instance1   | 3.0 
| data 2 | 'this is an example of instance2| 4.0

Note that I have to do this for over 60 tables not just 2. There are also other tables with different information in the same database, so I cant just join all tables in there.

Upvotes: 0

Views: 276

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You really need to fix your data structure. You should not be storing data in tables with the same structure -- that information should all go into a single table. Then you wouldn't have this issue.

For now, you can construct a view with all the data. You can generate the code for the view with something like this:

SELECT CONCAT('CREATE VIEW vw_dir AS',
              GROUP_CONCAT(REPLACE('SELECT NAME, INST_NAME, `DROP` FROM [T]', '[T]'), TABLE_NAME)
                           SEPARATOR ' UNION ALL '
                          )
             ) as create_view_sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_dir%';

Then take the SQL, run it, and you'll have a view called vw_dir. The next time you add a table, you'll need to drop the view and then recreate it.

With this solved, you can now start thinking about how to get all the data into a single table, without having the intermediate tables cluttering up your database.

Upvotes: 2

Related Questions