CODEWITHSUNDEEP
mysql
Parviz Sattorov
Parviz Sattorov

Reputation: 113

MySQL: getting table names from another table and use it for query

I am doing a sync process for a client-server application. Only several tables need to be synced, but the number of tables may change, so i decided to store sync'able table names in a separate table:

sync_tables:

ID  |  tableName
----------------
1   |  table1
2   |  table2

I want to get 'version' column for each table.

table1:

ID  |  name  |  version
-----------------------
1   |  n1    |  22
2   |  n2    |  24

table2:

ID  |  name  |  version
-----------------------
1   |  n3    |  27
2   |  n5    |  29

I don't want to use

SELECT version FROM table1, table2

Question: How can i do something like this:

SELECT version FROM [sync_tables.getTableNames]

SOLUTION with PHP by using PaulF's answer:

mysql_query("SELECT CONCAT('SELECT `version` FROM ',GROUP_CONCAT(tableName SEPARATOR ' UNION ALL SELECT `version` FROM ')) from sync_tables into @myVar;");
mysql_query("PREPARE stmt FROM @myVar;");
$queryResult = mysql_query("EXECUTE stmt;");
mysql_query("DEALLOCATE PREPARE stmt;");

if( $queryResult )
{
    while($row = mysql_fetch_assoc($queryResult))
    {
        foreach($row as $cname => $cvalue)
        {
            print "$cname: $cvalue <br/>";
        }
    }
}
else
{
    echo "query returned empty<br/>";
}

Upvotes: 2

Views: 1613

Answers (1)

PaulF
PaulF

Reputation: 6773

Your first query would result in an error due to an ambiguous Column name, but if you used full qualified column names it would result in a cross-join giving every possible combination of version numbers from the tables.

You need to use a union - so the query would be

SELECT version FROM table1
UNION ALL
SELECT version FROM table2

MySQL does not allow the table name to be included in the query as a variable - so as you don't want to write a fixed query, but use the results from another query - you would need to build up a string to create the full query & then run the query as a Prepared Statement : see docs here http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

The tables name from sync_table can be extracted & combined into a single comma-separated string with the GROUP_CONCAT function. The resulting string can be modified with the CONCAT & REPLACE functions to create your required query & then executed as a Prepared Statement as follows :

SELECT GROUP_CONCAT(tablename) from sync_tables into @var;
SELECT CONCAT('SELECT `version` FROM ',REPLACE( @var, ',', ' UNION ALL SELECT `version` FROM ' )) INTO @var2;
PREPARE stmt FROM @var2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The first two lines could be combined as 

SELECT CONCAT('SELECT `version` FROM ',GROUP_CONCAT(tablename SEPARATOR ' UNION ALL SELECT `version` FROM ') from sync_tables into @var2;

NOTE 'UNION ALL' is used rather than just 'UNION' so duplicates are not omitted, if you don't want duplicates then miss out 'ALL'

Upvotes: 3

Related Questions