How to `SELECT FROM` a table that is a part of a query itself using MySQL?

Question

Say, if I have multiple tables that have the same schema:

CREATE TABLE `tbl01`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` TINYTEXT,
  `data` INT
);

CREATE TABLE `tbl02`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` TINYTEXT,
  `data` INT
);

CREATE TABLE `tbl03`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` TINYTEXT,
  `data` INT
);

-- etc. ------------------

INSERT INTO `tbl01` (`name`, `data`) VALUES
('row 1', 1),
('row 2', 1),
('row 3', 3);

INSERT INTO `tbl02` (`name`, `data`) VALUES
('cube', 1),
('circle', 0);

INSERT INTO `tbl03` (`name`, `data`) VALUES
('one', 1);

and then one table that contains names of all other tables in one of its columns:

CREATE TABLE `AllTbls`
(
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tblnm` VARCHAR(64) NOT NULL UNIQUE,
  `desc` TINYTEXT,
  `flgs` BIGINT UNSIGNED
);

INSERT INTO `AllTbls` (`tblnm`, `desc`, `flgs`) VALUES
('tbl01', 'Table 1', 0),
('tbl02', 'Table two', 1),
('tbl03', '3rd table', 0);

So if I want to write a query to retrieve contents of AllTbls and also in one column to include count of rows in each of corresponding tables, I thought the following would be the way to do it:

SELECT *, `tblnm` as TblName, (SELECT COUNT(*) FROM TblName) as cntRws 
  FROM `AllTbls` ORDER BY `id` ASC LIMIT 0,30;

But this returns an error:

#1146 - Table 'database.TblName' doesn't exist

I know that I can do this in multiple queries (using a loop in a programming language), but is it possible to do it in one query?

PS. I'm using MySQL v.5.7.28

Can new tables be created at any time, by some external process over which you have no control? — Caius Jard, Apr 04 '20 at 04:15
@TimBiegeleisen and `dynamic SQL` is what? when I compose it "on the fly" from a programming language. — c00000fd, Apr 04 '20 at 04:20
I don't think you can use/interpolate a SELECTED field as a FROM argument. Maybe if you explore upper level querying of the information schema? — Martin Buezas, Apr 04 '20 at 04:23
@c00000fd Yes, either that, or you would need to use a stored proc or a prepared statement from MySQL. Regular MySQL can't be used for this. — Tim Biegeleisen, Apr 04 '20 at 04:25

Caius Jard · Answer 1 · 2020-04-04T04:38:15.880

The simple answer is: "you can't"

Table names are not supposed to be used like variables, to hold data, in this way. What you're supposed to have is one table:

tblContractCounts
Client, ContractCount
-------------------
IBM, 1
Microsoft, 3
Google, 2

Not three tables:

tblIBMContractCounts
ContractCount
1

tblMicrosoftContractCounts
ContractCount
3

tblGoogleContractCounts
ContractCount
2

If your number of tables is known and fixed you can perhaps remedy things by creating a view that unions them all back together, or embarking on an operation to put them all into one table, with separate views named the old names so things carry in working til you can change them. If new tables are added all the time it's a flaw in the data modelling and need to be corrected. In that case you'd have to use a programming language (front end or stored procedure) to build a single query:

//pseudo code
strSql = ""
for each row in dbquery("Select name from alltbls")
  strSql += "select '" + row.name + "' as tbl, count(*) as ct from " + row.name + " union all "
next row
strSql += "select 'dummy', 0"

result = dbquery(strSql)

It doesn't have to be your front end that does this - you could also do this in mysql and leverage the dynamic sql / EXECUTE. See THIS ANSWER how we can concatenate a string using logic like above so that the string contains an sql query and then execute the query. The information schema will give you the info you need to get a list of all current table names

But all you're doing is working around the fact that your data modelling is broken; I recommend to fix that instead

ps: the INFORMATION_SCHEMA has rough counts for tables with their names, which may suffice for your needs in this particular case

select table_name, table_rows from infornation_schema.tables where table_name like ...

Thanks for the explanation. – c00000fd Apr 04 '20 at 04:43 — c00000fd, Apr 04 '20 at 04:43

Erick Vargas Arias · Answer 2 · 2020-04-05T22:51:36.807

I managed to solve the problem using the following stored procedure.

-- DROP PROCEDURE sp_Count_Rows;
Delimiter $$
CREATE PROCEDURE sp_Count_Rows() 
BEGIN
DECLARE table_name TEXT DEFAULT "";
DECLARE finished INTEGER DEFAULT 0;
DECLARE table_cursor 
CURSOR FOR 
    SELECT tblnm FROM alltbls;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN table_cursor;
DROP TABLE IF EXISTS RowsCount;
CREATE TABLE IF NOT EXISTS RowsCount(Tlbnm text, ctnRws int);

table_loop: LOOP

    FETCH table_cursor INTO table_name;

    IF finished = 1 THEN 
        LEAVE table_loop;
    END IF;

    SET @s = CONCAT("insert into RowsCount select '", table_name ,"', count(*) as cntRws from ", table_name);
    PREPARE stmt1 FROM @s; 
    EXECUTE stmt1; 
    DEALLOCATE PREPARE stmt1; 

END LOOP table_loop;
CLOSE table_cursor;

SELECT * FROM RowsCount;
DROP TABLE RowsCount;
END
$$

And then when you call the procedure

CALL sp_Count_Rows();

You get this result

Thanks. I'll have to review it. Unfortunately my MySQL version is only `5.7.28`. What makes it dependent on v.8? — c00000fd, Apr 04 '20 at 06:16
Nothing specific that I know of, but it was in case you had to make a modification to the code or maybe the database manager gave some warning / problem. — Erick Vargas Arias, Apr 04 '20 at 06:22

How to `SELECT FROM` a table that is a part of a query itself using MySQL?

2 Answers2