simplifying a MySQL query, pulling table name dynamically from table data

Question

Hello

I have a mysql table and one of the fields is 'template'. This field will point to the correct template table to reference the unique fields for that particular template.

So for example if table.template = 'news', I also need to pull the info from the news_template table to have the complete set of listing info. Right now I only know how to do this with two separate query calls from PHP.

Is there anyway to do a join with a dynamic table name based on the data in one of the columns?

Thanks in advance!

Answer 1

I don't think this can be done directly in MySQL without splitting it into multiple queries.

I usually see this handled by querying for the info needed to determine the correct table name, then the scripting language being used (PHP?) puts together another query using that information.

Answer 2

If you can, I would suggest restructuring your *_template tables into a new table called template_data. This table can then have a column called template_name. Then selecting the correct data becomes a simple SQL query, eg.

select Parameter, Value from template_data where template_name = ?