How to create a SELECT statement involving a subtype

Question

Okay, so all I'm trying to do here is "list" the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998.

My issue is that I don't know how to select the managers' name because they're listed under MANAGER, which is a subtype of the EMPLOYEE supertype. I've posted my EER diagram below:

Walter Mitty · Answer

How you do the select depends on your table design.  Your EER depicts the entities, relationships and attributes nicely, but doesn't say anything about table design.  (That's as it should be, IMO).  A relational schematic would give those details.

If you are interested in how to design tables for a subtype (or subclass) situation here are two alternative techniques for you to explore:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

And there is a technique often used when tables have a one to one relationship to each other.  You can see it here:

https://stackoverflow.com/questions/tagged/shared-primary-key

How to create a SELECT statement involving a subtype

One Answer

Add your own answers!

Ask a Question