2

I have a mysql database that contains multiple tables with the same columns (not my design decision). These tables are named dynamically like codes_1, codes_2.... I would like to execute a query that will take the contents (rows) of codes_1, and codes_2..., and merge them together into one result.

I already have sql that can return the names of the tables using SCHEMA_INFORMATION.table_name, but I cannot figure out how to query each table based on the name, and how to merge the result of each table into one result.

So in reality here are the two questions:

  1. how can I query multiple tables based on the table names that are returned as a query result?

  2. how can I, after doing a select on each table, merge all the rows from the tables into one result?

Nick Sallis
  • 33
  • 4
  • What do you mean by "merge them into one result". Do you mean add them together? Or something else? An example would go a long way here... – lurker Jan 25 '18 at 17:57
  • Look into UNION. But also post the schema, or even a screenshot of the tables so we can see the columns in each (i know you said they are the same but its really helpful to be able to see it...) – Stuart Jan 25 '18 at 17:57
  • 1
    Maybe use a UNION? – Ottawa Jan 25 '18 at 17:59
  • @lurker I will have one query that will get the table names. then another query that will get all columns for each table with a name in the first query results. That will result in rows from table "codes_1", rows from table "codes_2", etc. I will then need to "merge" all of these rows together to return them. – Nick Sallis Jan 25 '18 at 18:10
  • (not my design decision) -- luckily, you're now in command. – Strawberry Jan 25 '18 at 18:11
  • @Strawberry yes I'm still not sure what people were thinking with this... – Nick Sallis Jan 25 '18 at 18:14
  • I see. Do all of the tables have the same attributes? – lurker Jan 25 '18 at 18:22
  • @lurker yes, they do. The columns are all the same. – Nick Sallis Jan 25 '18 at 18:24
  • 1
    Maybe something from these answers will help: [MySQL Loop Through Tables](https://stackoverflow.com/questions/12718596/mysql-loop-through-tables) and [How to loop through all the tables on a database to update columns](https://stackoverflow.com/questions/37313983/how-to-loop-through-all-the-tables-on-a-database-to-update-columns). These solutions don't directly answer your specific scenario, but the principles may get you there. In the end, you might end up creating a new table and iterating through the others, selecting from them and updating the new table with the results. – lurker Jan 25 '18 at 18:49
  • @lurker that first link looks to be helpful. Thanks! – Nick Sallis Jan 25 '18 at 19:09

1 Answers1

4

Is this what you're looking for? Edit the colums to your preference, you never specified your table names so I just named them table_1 and table_2.

SELECT codes_1, codes_2, codes_3 FROM `table_1`
UNION 
SELECT codes_1, codes_2, codes_3 FROM `table_2`
Ottawa
  • 164
  • 4
  • codes_1 and codes_2 are table names. there might also be hundreds more. I get the names as a sql query to SCHEMA_INFORMATION since the names are all similar save the last number. – Nick Sallis Jan 25 '18 at 18:07
  • 1
    FYI, this is NOT the answer. Codes_1, codes_2... are TABLE NAMES, not column names! – Nick Sallis Jan 25 '18 at 18:21