Retrieving records from design containing multiple hierarchy

I have 4 tables (Type01, Type02, Type03, Type04) . They have different columns . Table Typ开发者_Python百科e01 is a parent of 3 child tables (subtype01, subtype02, subtype03) . And it has a one to one relationship with its children (each record in parent corresponds to one record that exists in only one of the children) . I want to retrieve all (to make life easier) records of all of the 4 tables including related children by order (date of insertion) . So, I created a super table having universal ID for types . See the figure please .

Now, what is the sql query for retrieving all records of the 4 tables including children of table Type01 ? and is this the most efficient design (space & retrieving speed) ?

Thanks a lot .

Try:

SELECT Types.ID, COALESCE(SubType1.Name, Type2.Name, Type3.Name) as Name,
  COALESCE(SubType2.Description, Type1.Description, Type2.Description) as Description,
  ...
FROM Types
  LEFT JOIN SubType1 ON (Types.ID = SubType1.ID)
  LEFT JOIN SubType2 ON (Types.ID = SubType2.ID)
  ... 
WHERE ...
ORDER BY Types.Insert_Date;

Fill in the various columns you want to return, add LEFT JOINs for all leaf node tables, and add your WHERE conditions.