query to select common fields from dynamic list of tables

I have a database that I created in MySQL of 1 minute stock prices. 开发者_C百科The database is setup so each stock has its own table named by its symbol containing Date, Open, High, Low, Close, Volume. Also there is a table called symbols that has Symbol, Type. The ultimate goal of the database is to be able to get prices for a range of dates from a collection of symbols.

this query works for selecting a subset of symbols from the symbols table:

SELECT symbol 
FROM minute.symbols 
WHERE type = 'ETFs';

this query works for selecting a subset of dates from a single stock:

SELECT 'SPY', date, close 
FROM minute.SPY 
WHERE date > '2000-01-04 09:30:00' AND date <= '2000-01-04 10:00:00';

this query works for selecting common prices from two stocks:

(SELECT 'SPY', date, close 
 FROM minute.SPY 
 WHERE date > '2000-01-04 09:30:00' AND date <= '2000-01-04 10:00:00'
) 
UNION 
(SELECT 'QQQQ', date, close 
 FROM minute.QQQQ 
 WHERE date > '2000-01-04 09:30:00' AND date <= '2000-01-04 10:00:00'
)

i'd like to be able to combine query #1 and query #2 into one query to get results like query #3, but for all tables returned by query #1.

is this feasable and efficient or should i instead dynamically build query #3 which may result in 100s of UNIONS?

---

You could achieve this by using Stored Procedure.

• You would pass type and the date ranges as parameters.
• You would run a cursor over the first table (symbols)
• Loop over the cursor and build the SQL string with help of Concat
• In the end when you have the query built, you make is of Prepare and Execute to run it