Nested Select Statements

[BigBrother]

Does anyone know if there's a way to create a select statement where the FROM clause references tables listed in a field from another table?

So, for example let's say I have a database that stores data on 3,000 different stocks accross the nyse. Assume each stock has its own table with the following fields: date, open, high, low, close, volume. A Tickers table stores each stock's symbol, name, and table_name where its data can be found.

So the question is, is there a way to construct a SELECT statement that will use the table_name field in the Tickers table to select data accross the other 3,000 stock tables?

 

[rycamor]

Yes, in theory, but that would be one Loooooonnnggg query. I doubt that MySQL would support a query that long.

You can easily join tables in queries:

SELECT table1.field,table2.field,table3.field FROM table1,table2,table3 WHERE table1.field = 'value' AND table2.field = 'value' AND table3.field = 'value';

So your query would be many thousands of characters long; unwieldy at best, even if it works. If you are really fixated on the 3000 tables, approach, what you should do is use a programming language to loop through each table, and select your specific records into a temp table, where you can do other queries on that unified group of records.