SELECT but with order controlled by data in another table

Question

I have a query

$STH_1 = $DBH_R->query("SELECT table_name                        
                         FROM information_schema.tables                        
                        WHERE table_name                        
                         LIKE 'v_c_ei\_9%'
                         ");

$stmts_1 = array();

while (($row_1 = $STH_1 -> fetch_assoc()) !== null){
$table_name = $row_1['table_name'];

The result of this query is, generally, the list of the tables in order:

v_c_ei_9001
v_c_ei_9002
...........
v_c_ei_9030

In another table, v_c_country, I have 2 columns: table_name and country_name, (because for example, v_ei_c_9001 is Australia, v_ei_c_9012 is Great Britain etc.)

Generally, as the result, I need the list of the tables (table names) from query above but in ORDER ASC from country names from table v_c_country.

How to change the query?

score 1 · Accepted Answer · answered May 12 '12 at 16:47

SELECT t.table_name                        
  FROM information_schema.tables AS t
  JOIN v_c_country               AS c ON t.table_name = c.table_name
 WHERE t.table_name LIKE 'v_c_ei\_9%'
 ORDER BY c.country_name ASC;

For testing and verification, add c.country_name to the select-list.

score 0 · Answer 2 · answered May 12 '12 at 14:02

0

You don't. The rule in MySQL is, you can never mix data (e.g. a country name stored in a table) and metadata (e.g. a table name).

What you can do is using a stored procedure to abuse statement preparation as dynamic SQL. This has been described here at SO many times.

answered May 12 '12 at 14:02

Eugen Rieck

64,175
10
70
92

thnks, but I never find any question how compare metadata (table_name) with data (table_name) – Andrew May 12 '12 at 14:10
This is the main point: You can't compare data and metadata in MySQL by any other way than dynamic SQL – Eugen Rieck May 12 '12 at 14:16
I think you've missed the point. The table names are simply data; the application program is going to get the names in sequence, and probably generate appropriate SQL dynamically with those names. You're right that you can't select the table names and select from those names in a single query (that would be mixing metadata and data), but the query simply needs the table names (data when looked at in this light) in a particular sequence. – Jonathan Leffler May 12 '12 at 16:49
thanks for all. We have solution for this problem - but with a little work from backside of it. see at http://stackoverflow.com/questions/10564602/query-with-verified-if-table-exist . We use the table names and data from an extra table and it's working. When it's working from backside, I think, it must work from frontside too. – Andrew May 12 '12 at 21:39

SELECT but with order controlled by data in another table

2 Answers2