select column from multiple unknown tables

uservyih 0
Viewed 46

I have a lot of dynamically created nearly similar looking tables according to the scheme "prefix + number", eg "t1", "t2", "t343" etc. All those tables have a cross-table unique row named identifier that I like to select within one query:

SELECT
  `identifier`
FROM
(
  SELECT
    `TABLE_NAME`
  FROM
    information_schema.TABLES
  WHERE
    `TABLE_NAME` LIKE 't%'
);

But this returns: ERROR 1248 (42000): Every derived table must have its own alias

EDIT: according to the comments I modified my query like this:

SELECT
  A.identifier
FROM
(
  SELECT
    `TABLE_NAME` AS identifier
  FROM
    information_schema.TABLES
  WHERE
    `TABLE_NAME` LIKE 't%'
) A;

But this selects only the table names from the subquery but not the column identifier from these tables.

mariadb mysql
1 Answers

When you create the table dynamically, and you want to query all of them, you can create an SQL statement dynamically like:

select
   group_concat(
   concat(
       'SELECT ',
       '''',TABLE_SCHEMA,'.',TABLE_NAME,''',',
       TABLE_SCHEMA,'.',TABLE_NAME,'.', COLUMN_NAME,
       ' FROM ',
       TABLE_SCHEMA,'.',TABLE_NAME)
      separator ' union all ')
from information_schema.`COLUMNS` c  
where table_schema='test'             -- the schema name where your tables are
  and table_name  regexp '^t[0-9]+$'  -- table name starts with t and ends with number
  and COLUMN_NAME = 'i'               -- i choose `i` as the column to be selected
;

This will produce a SQL statement like:

select
    'test.t1',
    test.t1.i
from
    test.t1
union all
select
    'test.t2',
    test.t2.i
from
    test.t2
union all
select
    'test.t3',
    test.t3.i
from
    test.t3

When putting all of this in a stored procedure, you can use PREPARE and EXECUTE to execute this created statement.

Above is just an example of an SQL statement, you should change it to your needs.

Luuk0
Related