How to automate selection of a column from multiple tables-CodePudding

I have several tables at least 26 ... i'm trying to select a column from all tables and displaying them as one

i tried this :

(SELECT col1,col2,col3,col4 
 FROM table1 
 ORDER BY col1 DESC LIMIT 1)
 UNION 
(SELECT col1,col2,col3,col4 
 FROM table2 
 ORDER BY col1 DESC LIMIT 1)

this works but i have to copy and paste a bunch of times depending on how many number of tables i have which isn't very effective . Please Help -- i just started learning mysql and i'm stuck trying to fix this.

CodePudding user response：

You can do it by using a cursor inside a stored procedure, like this:

CREATE DEFINER=`homestead`@`%` PROCEDURE `union_tables`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tablename VARCHAR(100);
  DECLARE tableschema VARCHAR(100);
  DECLARE sql_union_tables TEXT;
  DECLARE tables_cursor CURSOR FOR SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'DbInscripciones' AND TABLE_NAME LIKE 'TblFaltasA%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN tables_cursor;
  
  SET sql_union_tables = '';

  read_loop: LOOP
    FETCH tables_cursor INTO tablename, tableschema;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF sql_union_tables = '' THEN
      SET sql_union_tables = CONCAT('(SELECT numcontrol FROM ', tableschema, '.', tablename, ' LIMIT 1)');
    ELSE
      SET sql_union_tables = CONCAT(sql_union_tables, ' UNION ALL (SELECT numcontrol FROM ', tableschema, '.', tablename, ' LIMIT 1)');
    END IF;
  END LOOP;

  CLOSE tables_cursor;
  
  SET sql_union_tables = CONCAT(sql_union_tables, ';');
  
  PREPARE stmt FROM sql_union_tables;
  EXECUTE stmt;
  
  SELECT sql_union_tables;
END

Let's explain this by parts. You get a list of desired table names by querying the information_schema database, and the tables_cursor cursor will allow you to iterate over that table list.

In the iteration part you construct a query using the tables obtained from the cursor query and save it in sql_union_tables.

After you finish constructing the query you execute it with the PREPARE and EXECUTE statements and also return the resulting query (last line).

This stored procedures assumes you have the same columns in your tables. If your columns vary for each table you'll have to implement some logic to deal with that.

After this you call the stored procedure like this:

CALL union_tables