Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a bit of a DBA newbie here (coming from a programming background having to fill the role of a DBA), so please excuse by blantant and somewhat basic ignorance.

Using a simple relationship between a supertype VEHICLE and its relative subtypes that are actual vehicles, like cars, boats or planes etc. If you query the Vehicle table to get some results, say, show me all vehicles that have a colour = RED, then how does the database know how to query the CAR table, seeing as that is the only table in this example that has a column to do with colour.

I supsect I must have some kind of vehicle type lookup type table, which should also point to the table relevant for that subtype, but I'm not sure if I'm over complicating things or if this is even good practice. Any help by experienced, wiser heads would be much appreciated.

VEHICLE (supertype)

╔════════╦══════════╗
║ VEH_ID ║  FOO_COL ║
╠════════╬══════════╣
║     28 ║ foo      ║
║     52 ║ column   ║
║     18 ║ data     ║
╚════════╩══════════╝

CAR (subtype)

╔════════╦══════════╗
║ CAR_ID ║  COLOUR  ║
╠════════╬══════════╣
║    52  ║ red      ║
╚════════╩══════════╝

BOAT (subtype)

╔════════╦══════════╗
║ BOAT_ID║  SPEED   ║
╠════════╬══════════╣
║    18  ║ VERY FAST║
╚════════╩══════════╝
share|improve this question
    
Can you add the full table scripts please? –  gbn Apr 30 '12 at 12:46
add comment

1 Answer 1

up vote 2 down vote accepted

Unless you use dynamic SQL to build the query (which adds no value anyway) then SQL is a "fixed column" language. That is, you'd have to send all columns you need. This example is bad in that you have all columns but you get the idea.

SELECT
   V.*,
   C.*,
   B.*
FROM
   Vehicle V
   LEFT JOIN Car C  ON V.VEH_ID = C.CAR_ID
   LEFT JOIN Boat B ON V.VEH_ID = B.BOAT_ID

Another option is to hide the VEHICLE-CAR join behind a view "CARS" and VEHICLE-BOAT join behind another view "BOATS". However, you can't UNION them as the have different columns and types

Basically, you don't query supertypes/subtypes like this because each subtype gives a different resultset.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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