MySQL Using Subquery To Get Table Name?

Question

I'm trying to select some data from a MySQL database.

I have a table containing business details, and a seperate one containing a list of trades. As we have multiple trades

business_details

id | business_name | trade_id | package_id
1  | Happy News    |    12    |    1

This is the main table, contains the business name, the trade ID and the package ID

shop_trades

id | trade
1  | newsagents

This contains the trade type of the business

configuration_packages

id | name_of_trade_table
1  | shop_trades
2  | leisure_trades

This contains the name of the trade table to look in

So, basically, if I want to find the trade type (e.g., newsagent, fast food, etc) I look in the XXXX_trades table. But I first need to look up the name of XXXX from the configuration_packages table.

What I would normally do is 2 SQL queries:

SELECT business_details.*, configuration_packages.name_of_trade_table
FROM business_details, configuration_packages
WHERE business_details.package_id = configuration_packages.id
AND business_details.id = '1'

That gives me the name of the database table to look in for the trade name, so I look up the name of the table

SELECT trade FROM XXXX WHERE id='YYYY'

Where XXXX is the name of the table returned as part of the first query and YYYY is the id of the package, again returned from the first query.

Is there a way to combine these two queries so that I only run one?

I've used subqueries before, but only on the SELECT side of the query - not the FROM side.

Answer 1

That's not possible, and it sounds like a problem with your model.

Why don't you put shop_trades and leisure_traces into the same table with one column to distinct between the two?

Answer 2

Typically, this is handled by a union in a single query.

Normalization gets you to a logical model. This helps better understand the data. It is common to denormalize when implementing the model. Subtypes as you have here are commonly implemented in two ways:

• Seperate tables as you have, which makes retrieval difficult. This results in your question about how to retreive the data.
• A common table for all subtypes with a subtype indicator. This may result in columns which are always null for certain subtypes. It simplifies data access, and may alter the way that the subtypes are handled in code.

If the extra columns for a subtype are relatively rarely accessed, then you may use a hybrid implementation where the common columns are in the type table, and some or all of the subtype columns are in a subtype table. This is more complex to code.