How do I SELECT from a Table, whose name is contained in a column?

[leash123]

Okay, weird issue...

 

I have a 6 tables, all with the same structure, but I've separated them to reduce computation time (each table corresponds to a category, and so the 6 tables are independent of one another).

 

I'm doing a SELECT statement from another table, which has one column which contains the name of one of the 6 tables. Because I'm trying to do this all in one a single SQL call, what I'm trying to do (but this won't work) is:

 

SELECT id, (SELECT name FROM a.table_name WHERE id=a.id) as name FROM linkage_table a;

 

Where linkage_table contains a bunch of columns, including the column table_name, which is the name of the table that I want to do the inner-select statement...

 

MySQL returns the error that a.table_name doesn't exist.

 

Any help?

 

Sorry if this is tough to understand, Let me know if I didn't explain it well, and I'll try again.

[leash123]

So I've decided to do a CASE select, and compare it as a string. I'd rather not do it this way, but I can't figure out any alternative.

[effigy]

It sounds like you need to modify your database design. You can also try using PHP to run multiple queries, or creating a stored procedure.

[leash123]

I think a stored procedure would be my other option. I want to avoid using PHP to do any lazy loading, 'cause computation time would skyrocket (there's a lot that'll be going on, so if I can keep it to one statement that'd be best)

[fenway]

Okay, weird issue...

 

I have a 6 tables, all with the same structure, but I've separated them to reduce computation time (each table corresponds to a category, and so the 6 tables are independent of one another).

 

I'm doing a SELECT statement from another table, which has one column which contains the name of one of the 6 tables. Because I'm trying to do this all in one a single SQL call, what I'm trying to do (but this won't work) is:

 

SELECT id, (SELECT name FROM a.table_name WHERE id=a.id) as name FROM linkage_table a;

 

Where linkage_table contains a bunch of columns, including the column table_name, which is the name of the table that I want to do the inner-select statement...

 

MySQL returns the error that a.table_name doesn't exist.

 

Any help?

 

Sorry if this is tough to understand, Let me know if I didn't explain it well, and I'll try again.

 

You can't do choose the table name dynamically... do it in 2 sql statements, or decide programmtically.

[roopurt18]

I'm not sure if you meant this when you said you would use a CASE, but you could LEFT JOIN each of the possible tables and return the one where `name` IS NOT NULL.  Could be performance issues with that though.

[fenway]

I'm not sure if you meant this when you said you would use a CASE, but you could LEFT JOIN each of the possible tables and return the one where `name` IS NOT NULL.  Could be performance issues with that though.

 

Yeah... bad idea... since you don't have to "guess" the table name, do it in php.