How to JOIN a table with a name that equals to a determined value in MySQL? The model of the (PHP &amp; MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here.

To map this to the database I’m using the Class Table Inheritance design pattern.

I’m cheating a bit and let the “super-entity” (i.e., the supertype) know, which type of “sub-entity” (i.e., the subtype) belongs to it. That means, the “super-entity” has a property (i.e., attribute or column) named type. The value is camel-cased (for an easier usage in the application). The table names are always underscored.

Here is a diagram depicting my database structure



And a diagram displaying class models for my application program objects



The problem is now the retrieving. When I want to get a Car with id=123, I don't know at first, which type it has and hence which car_* table I have to JOIN. Well, I could workaround it, either JOINing  every time all car_* tables or making two request (when the first one is something like a “pre-request” and is used to get the type: SELECT type FROM car;), but I’m sure, there must be a better solution.

How to SELECT/JOIN a TABLE with a variable name, that equals to a selected value in another TABLE?
You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out.

Drawing in all the tables as follows would work:

SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2
     , ct.specialAttributeA
     , ca.specialAttributeB
     , cb.specialAttributeC
FROM   AbstactCar ac
LEFT OUTER JOIN
       CarTesla ct ON ct.carID = ac.CarID
LEFT OUTER JOIN
       CarAstonMamn ca ON ca.carID = ac.CarID
LEFT OUTER JOIN
       CarBMW cb ON cb.carID = ac.CarID


but obviously this is limited by needing to know all the types in advance and/or update the query every time a type is added/dropped/changed and possibly hitting some limit on the number of tables in any given SELECT. If a non-common attribute can appear in more than on of the sub-classes then change the definition for that output column to something like ca.specialAttributeD, cb.specialAttributeD AS specialAttributeD.

NOTE: I'm pretty sure the above is mysql compatible, but I'm generally an MS SQL Server fellow and haven't tested it so you may need to tweak the syntax slightly.