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

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.

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

StackExchange Database Administrators Q#16480, answer score: 4