How can I select using the same fields from an undetermined number of tables using MySQL?

Question

I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this:

foo_reference1
foo_reference2
foo_referencea
foo_referenceb
....
foo_referencez

The tables all contain the fields foo_id, bar_id and bar_weight. I need to get the top five bar_id fields ordered by bar_weight (descending) where foo_id equals something from each table.

The problem is, no two servers will have the exact same number of tables, or the same name. However, they always start with foo_reference. I need to run this in dozens of places. In an ideal world, there would just be one properly indexed foo_reference table, unfortunately changes just aren't possible.

Initially, I tried just using SHOW TABLES LIKE 'foo_reference%' in a sub query in order to build the list of tables that have to be queried. Apparently, MySQL does not like that, so I just queried the information schema directly:

select bar_id, bar_weight from
(
    select table_name as name
    from information_schema.tables as tmp
    where tmp.table_name like 'foo_reference%'
) as res
where res.foo_id = '1'
order by res.bar_weight desc
limit 0, 5;

MySQL is telling me that bar_id is an unknown column in the field list. When I run the sub query by itself, it returns the list of tables that need to be queried.

What am I doing incorrectly? All I want are the top 5 bar_id fields from each table where foo_id is a certain number. As you can tell, I'm doing a bit of learning (and quite a lot of guessing) as I go here.

Answer 1

You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):

SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
    select CONCAT(table_schema,'.',table_name) tb
    from information_schema.tables
    where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

First, let's make some sample tables with sample data:

DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
    foo_id int,
    bar_id int,
    bar_weight int,
    primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);

Here are the sample tables being made and loaded:

mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)

mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)

mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)

mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
    -> (
    ->     foo_id int,
    ->     bar_id int,
    ->     bar_weight int,
    ->     primary key (foo_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

Now let's build the query and execute it:

mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
    -> GROUP_CONCAT(tb SEPARATOR
    -> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
    -> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
    -> INTO @foo_query FROM
    -> (
    ->     select CONCAT(table_schema,'.',table_name) tb
    ->     from information_schema.tables
    ->     where table_name like 'foo_reference%'
    -> ) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)

mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
|     27 |         90 |
|     47 |         70 |
|     67 |         50 |
|     37 |         40 |
|     57 |         20 |
+--------+------------+
5 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

CAVEAT

Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.

Also, notice that the subquery will get 6 rows, and the limit 0,5 properly displays the first 5 rows of the subquery.

Give it a Try !!!

Answer 2

As far as I know, the only way you can dynamically put something in FROM is to use Prepared Statements. Also, I believe you should use UNION to get results you want, not cross join. For instance, you result query should look like

SELECT * FROM 
(
   select bar_id, bar_weight from table1
   UNION 
   select bar_id, bar_weight from table2
)a
WHERE ... ORDER BY ... LIMIT ... 

Not

select bar_id, bar_weight 
from table1,table2
WHERE ... ORDER BY ... LIMIT ... 

Answer 3

There is a misunderstanding here:

select bar_id, bar_weight from
(
    select table_name as name
    from information_schema.tables as tmp
    where tmp.table_name like 'foo_reference%'
) as res
where res.foo_id = '1'
order by res.bar_weight desc
limit 0, 5;

This does not do what you think it is doing: the result of the subselect is not pasted into the query macro-style, becoming:

select bar_id, bar_weight from foo_reference1 as res
where res.foo_id = '1'
order by res.bar_weight desc
limit 0, 5;

Instead, it is as if you are querying a table with the following data in it:

select table_name as name
from information_schema.tables as tmp
where tmp.table_name like 'foo_reference%'

ands obviously there is no column called 'bar_id' in that table (just 'name').

As @a1ex07 mentions, it seems you can build up the SQL dynamically with union all and a prepared statement, or you may be able to use dynamic SQL in a stored procedure.