The model of the (PHP & 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

class model

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?

  • I don't think this is possible in any SQL RDBMS. You'll have to use two separate queries.
    – mustaccio
    May 6, 2016 at 17:28
  • You mean my first workaround? Well it's not nice, but it would work. But only when I want to get one result (WHERE id = 123). For result sets it's not even a workaround.
    – automatix
    May 6, 2016 at 18:13

1 Answer 1


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
       CarTesla ct ON ct.carID = ac.CarID
       CarAstonMamn ca ON ca.carID = ac.CarID
       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.

  • Thank you for your answer! It's a workaround and not a solution. But there also seems not to be a real solution in MySQL (at least for now). Anyway thanks again for trying to help!
    – automatix
    May 6, 2016 at 18:51
  • @automatix well then, I would say that only "solution" would be to use a different schema which is more suited for a relational DB.
    – jkavalik
    May 6, 2016 at 19:27
  • No, there isn't anything that mySQL and its descendants can do for you currently. PostgreSQL natively supports a form of table inheritance (postgresql.org/docs/current/static/ddl-inherit.html) which, particularly when combined with partitioning, may do what you are looking for if you were able to use that instead of mySQL. May 8, 2016 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.