Is it possible to create a select statement with the result of another select statement?

user_9380552 0
Viewed 41

Is it possible to create a select statement with the result of another select statement?

Important is, that Table2 can have a different number of columns.

Example:

Table1: 
+------+------+
| colA | colB | 
+------+------+
| colE |   0  | 
| colF |   0  | 
+--------+----+

Table2:
+------+------+------+
| colE | colF | colG |
+------+------+------+
|   1  |   1  |   1  |
|   1  |   1  |   1  |
+------+------+------+

select colA from Table1 where colB = '0';  
Result:
colE
colF

and now I want to sum all values of the columns in Table2 where the column names are the same as in the result above (sum of all values of colE and colF). The result should be 4 in this case.

I use mariaDB.

mariadb subquery sql
1 Answers

You can use Dynamic SQL along with information_schema.columns table, which provides information about columns in tables, in order to derive the columns of the Table2by joining to the Table1 to get only matching columns such as

SELECT GROUP_CONCAT( CONCAT('SUM(',colA,')') SEPARATOR '+' )
  INTO @sql
  FROM ( SELECT t1.colA 
           FROM( SELECT *
                   FROM information_schema.columns
                  WHERE table_name = 'Table2' ) AS c
           JOIN Table1 AS t1
             ON t1.colA = c.column_name ) AS t;

SET @sql = CONCAT('SELECT ',@sql,'  AS total FROM Table2'); 
                  
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

user_58413060
Related