select column from multiple unknown tables

Question

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.

Does this answer your question? [MYSQL ERROR 1248 (42000): Every derived table must have its own alias](https://stackoverflow.com/questions/3363918/mysql-error-1248-42000-every-derived-table-must-have-its-own-alias) OR [What is the error "Every derived table must have its own alias" in MySQL?](https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql) — Luuk, Sep 09 '22 at 10:48
No, not really. I'm not a mySQL expert. If I modify the query like `SELECT A.identifier FROM ( ... ) AS A`, I get **(42S22): Unknown column 'A.identifier' in 'field list'** and when I select `A.*` I get all the table names from the subquery as result. — Lars, Sep 09 '22 at 11:18
When you need a column name `identifier`, you should create an alias like: `SELECT x.identifier FROM ( SELECT TABLE_NAME as identifier FROM ... ) x;` — Luuk, Sep 09 '22 at 12:34
Or, you could do: `SELECT TABLE_NAME as identifier FROM ( SELECT TABLE_NAME FROM ... ) x;` Where `x` is the alias given to the result-table of the sub-query. — Luuk, Sep 09 '22 at 12:39
Doesn't work: `SELECT A.identifier FROM (SELECT TABLE_NAME as identifier FROM ...) A;` still outputs the table names from the subquery but does not select the "identifier" column from these tables. — Lars, Sep 09 '22 at 18:58
It is (very) unclear what you want to be returned from the query. What do you mean by the "identifier" column ? — Luuk, Sep 09 '22 at 20:02
I have dynamically created tabels like "t1", "t2", t3" etc but I don't now how much of them only the scheme: prefix "t" + number. Now I want to run a query on all of them like `select row1 form t1`, `select row1 form t2`, `select row1 form t3` etc. But I want do do this in one query so I thought I could get the table names from **information_schema.TABLES**. But I begin to understand that this seems to be the wrong approach because the result of the subquery is a table that contains all the table names as strings. Is there another way to reach my original goal? — Lars, Sep 11 '22 at 16:07
I edited the original question to make it hopefully clearer. — Lars, Sep 11 '22 at 16:11

score 0 · Answer 1 · answered Sep 12 '22 at 09:33

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.

select column from multiple unknown tables

1 Answers1