Use "show tables" as a derived table-CodePudding

I'm trying to combine "show tables like" as a derived table in a join.

This works:

  show tables like 'table_name_pattern_%'

However I cannot figure out how to make this work

  select i.* from table i
  left join (
      show tables like 'table_name_pattern_%'
  ) a on i.id=mid(a.`columnname_from_show_tables`, 20)

Any ideas, or is show tables a special statement that can't be joined?

I'm on MySQL 5.7

CodePudding user response：

You can use this:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'

Then:

 select i.* from table i
  left join (
 SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'
      AND TABLE_NAME like 'table_name_pattern_%'
  ) a on i.id=mid(a.`TABLE_NAME`, 20)