Question 281336 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Pull data from tables with different suffixes sql

I have multiple tables with the exact same structure (fields: date, id, revenue). Let’s imagine I have 3 tables, each one with 3 rows to make this simpler.

The only thing that changes between each table the is the suffix, for example:

Myserver.Mytable_name.05112021 (which we’ll call table 1)
Myserver.Mytable_name.06112021
(which we’ll call table 2)
Myserver.Mytable_name.07112021
(which we’ll call table 3)

What I want is to pull some fields in those tables such that I don’t have to specify the suffix

The result I expect should look as follows:

Id date revenue
id_1 date_1 revenue_1
id_2 date_1 revenue_1
id_3 date_1 revenue_1
id_1 date_2 revenue_2
id_2 date_2 revenue_2
id_3 date_2 revenue_2
id_1 date_3 revenue_3
id_2 date_3 revenue_3
id_3 date_3 revenue_3

How can I get the above result without using union nor union all?

This is important because my tables are created on a daily basis and I don’t want to be adding them manually within a CTE in order to get the data.

Thanks for your help!

Using union or union all is cumbersome in this case

Tags:

2

Answers


  1. 0

    This is important because my tables are created on a daily basis and I don’t want to be adding them manually within a CTE in order to get the data

    Then you will need to dynamically generate the SQL. For a MySQL solution start down this path: How To have Dynamic SQL in MySQL Stored Procedure

    The only other way to avoid this is to create some standard views that return the data from your tables, but have the process that created the tables re-create the views, then you could have a single query that does not need to be changed.

    • Either way, some process will need to change the views or change the query to reference the new tables. Dynamic SQL is the usual solution inside the database, but otherwise you could generate the query at the application layer, which would be the same principal.

    Looks to me like Union is perfect for this:

    Start with the manual query:

    https://sqlfiddle.com/#!9/afd29a/2

    SELECT STR_TO_DATE(RIGHT('Mytable_name.05112021',8),'%d%m%Y') as "date"
     , `id`
     , `revenue`
FROM `Mytable_name.05112021`
UNION ALL
SELECT STR_TO_DATE(RIGHT('Mytable_name.06112021',8),'%d%m%Y') as "date"
     , `id`
     , `revenue`
FROM `Mytable_name.06112021`
UNION ALL
SELECT STR_TO_DATE(RIGHT('Mytable_name.07112021',8),'%d%m%Y') as "date"
     , `id`
     , `revenue`
FROM `Mytable_name.07112021`;

    I’m not aware of a function we can use in MySQL to return the table name of a given column reference, but if we are going to use dynamic or prepared statements then it doesn’t matter.

    First we can generate the above SQL using this query:

    SELECT GROUP_CONCAT(CONCAT("SELECT STR_TO_DATE(RIGHT('", table_name, "',8),'%d%m%Y') as "date", `id`, `revenue` FROM `", table_name ,"`") SEPARATOR ' UNION ALL ') as Query 
FROM information_schema.tables
WHERE table_name like 'MyTable_name.%';

    Which returns this:

    SELECT STR_TO_DATE(RIGHT('mytable_name.05112021',8),'%d%m%Y') as "date", `id`, `revenue` FROM `mytable_name.05112021` UNION ALL SELECT STR_TO_DATE(RIGHT('mytable_name.06112021',8),'%d%m%Y') as "date", `id`, `revenue` FROM `mytable_name.06112021` UNION ALL SELECT STR_TO_DATE(RIGHT('mytable_name.07112021',8),'%d%m%Y') as "date", `id`, `revenue` FROM `mytable_name.07112021`

    So we can put all this together into a single SP:

    http://sqlfiddle.com/#!9/8ad1a3/3

    CREATE PROCEDURE dynamicQuery()
BEGIN
SET @query = (SELECT GROUP_CONCAT(CONCAT("SELECT STR_TO_DATE(RIGHT('", table_name, "',8),'%d%m%Y') as "date", `id`, `revenue` FROM `", table_name ,"`") SEPARATOR ' UNION ALL ') as Query 
              FROM information_schema.tables
              WHERE table_name like 'MyTable_name.%');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

    SQL fiddle doesn’t let you call SPs but it should work 😉

    Login or Signup to reply.
  2. 0

    You’ll need UNION/UNION ALL for this but there’s a way you write the query once and edit wherever necessary afterward. You can use PREPARED STATEMENT for that.

    Here is an example:

    SET @generatedQuery := NULL;

SELECT GROUP_CONCAT( CONCAT('SELECT * FROM ',table_name) SEPARATOR ' UNION ALL ')
         INTO @generatedQuery
 FROM information_schema.tables
WHERE 
 table_schema='Myserver' AND
 table_name LIKE 'mytable_%';

/*Check the generated query*/
SELECT @generatedQuery;

PREPARE statement FROM @generatedQuery;
EXECUTE statement;
DEALLOCATE PREPARE statement;

    In the example above, you’ll only need to focus on the query to generate the actual query. Let’s say that you want to get only specific date ranges, then you can do something like:

    SELECT GROUP_CONCAT(CONCAT('SELECT * FROM ',table_name,' WHERE date="2023-11-11"')) 
                 SEPARATOR ' UNION ALL ')
         INTO @generatedQuery
 FROM information_schema.tables
WHERE 
 table_schema='Myserver' AND
 table_name LIKE 'mytable_%';

    or maybe the database/server (table_schema) already change or the table_name prefix is now different, then:

    SELECT GROUP_CONCAT( CONCAT('SELECT * FROM ',table_name) SEPARATOR ' UNION ALL ')
         INTO @generatedQuery
 FROM information_schema.tables
WHERE 
 table_schema='newserver' AND
 table_name LIKE 'myNewtableName_%';

    Demo fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search