Is there a way to make a table name dynamic in a query?

Question

I am trying to create a Class-Inheritance design for products.

There is the base table that contains all the common fields. Then for each product type there is a separate table containing the fields that are for that product type only

So in order to get all the data for a product I need to JOIN the base table with whatever table that correlates to the product_type listed in the base table. Is there a way to make this query join on the table dynamically?

Here is a query to try to illustrate what I am trying to do:

SELECT * FROM product_base b
INNER JOIN <value of b.product_type> t
    ON b.product_base_id = t.product_base_id 
WHERE b.product_base_id = :base_id

Is there a way to do this?

Answer 1

No, there's no way to do this. The table name must be known at the time of parsing the query, so the parser can tell if the table exists, and that it contains the columns you reference. Also the optimizer needs to know the table and its indexes, so it can come up with a plan of what indexes to use.

What you're asking for is for the table to be determined during execution, based on data found row-by-row. There's no way for the RDBMS to know at parse-time that all the data values correspond to real tables.

There's no reason you would do this to implement Class Table Inheritance. CTI supports true references between tables.

You're instead describing the antipattern of Polymorphic Associations.

Answer 2

Make 2 queries:
First select < value of b.product_type > and then use it in the second one (the one that you have, but replace < value of b.product_type > with the result from the first one).

Answer 3

No. There would be little point even if it were possible, as the query optimiser would not be able to make a plan without knowing anything about the right- hand side of the join.

You need to construct the query using concatenation or similar, but make sure that you only use a valid table name to avoid injection attacks.

Answer 4

You can create a procedure that takes the table name as an argument and constructs a dynamic-SQL query. But it's probably easier to do this in your server-side code (PHP). But rather than make it a variable (and as suggested vulnerable to injection attacks), create separate classes for the different join combinations. Use another class (like a dispatcher) to determine the correct class to instantiate.