Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
"What am I doing incorrectly?" You have a highly unnormalized design. All those tables should be one table really. –  ypercube Apr 13 '12 at 15:19
    
@ypercube I noted that in my question. I can't change anything, I'm just trying to see if what I want to get is possible. (it's not my design). –  Tim Post Apr 13 '12 at 15:21
    
With this design you have, you'll have to use the INFORMATION_SCHEMA in one query, to get the names of the tables and then build - dynamically - the second query. No way - AFAIK - in one query. –  ypercube Apr 13 '12 at 15:21
add comment

3 Answers

up vote 3 down vote accepted

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 !!!

share|improve this answer
    
The cross checking isn't an issue in this case, I'm going to give this a try now. Thank you for spending so much time on this! –  Tim Post Apr 13 '12 at 17:05
    
I've got it working now. Thanks again for a solid answer that also points me to things I should probably read up on. –  Tim Post Apr 13 '12 at 17:15
1  
Two small things: 1) I'd use UNION ALL as it is faster and usually what you need. Unless you know for sure you want to filter duplicates. 2) If you have a WHERE clause, always do it in each of the "sub-queries" (so for each UNION), don't use one WHERE in the outer query. That way MySQL will use the indexes. –  Jannes Apr 16 '12 at 9:42
1  
Word to the wise, also make sure group_concat_max_len is large enough to not truncate the results of the information schema query. –  Tim Post Apr 16 '12 at 13:06
add comment

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 ... 
share|improve this answer
1  
do you mean UNION ALL? –  Jack Douglas Apr 13 '12 at 15:37
    
@Jack Douglas : I'm not sure if it has to be UNION ALL. UNION [DISTINCT] may also work depends on what results are expected. –  a1ex07 Apr 13 '12 at 16:09
    
Only UNION should suffice. I used UNION in my answer and it works. +1 for being first in suggesting Prepared Statements. –  RolandoMySQLDBA Apr 13 '12 at 16:46
    
@RolandoMySQLDBA "All I want are the top 5 bar_id fields from each table" - UNION [DISTINCT] will not give you that, will it? Wouldn't inter-table dupes be dropped? –  Jack Douglas Apr 13 '12 at 17:47
1  
@JackDouglas You are right, inter-table dupes would be dropped. Based on that phrase, the query should post up to 5 top rows per table and then collect those results. Perhaps the query should return the table name the data came from to make UNION ALL work properly because it is possible for two tables to have the same bar_id and bar_weight for a given foo_id. Only if 5 rows was expected in total would UNION suffice. –  RolandoMySQLDBA Apr 13 '12 at 18:09
add comment

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.

share|improve this answer
    
Thank you explaining the inverse of the answer since no one can should expect the metadata in the information_schema to write itself. +1 !!! –  RolandoMySQLDBA Apr 13 '12 at 21:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.