0

I use Oracle 11g and have a massive number of tables representing inheritance, where a base parent table has a primary key NUMBER ID. The subsequent tables inherit from it, representing through the shared primary key NUMBER ID. Let's assume there is a multiple layers of such inheritance.

To have a clear picture, let's work with the following simplified structure and assume the hierarchy is quite complex:

- TABLE FOOD
  - TABLE FRUIT
    - TABLE CYTRUS
      - TABLE ORANGE
      - TABLE GREPFRUIT
  - TABLE VEGETABLE
  - TABLE MEAT
    - TABLE BEEF
      - TABLE SIRLOIN
      - TABLE RIB EYE
    - TABLE CHICKEN

This is not taxative, regardless of how dumb the example is, assume such a multi-layered hierarchy using Class Table Inheritance (aka Table Per Type Inheritance).

If you want to insert a record to a table ORANGE having a certain generated ID, there must be inserted records to the parent tables (CYTRUS, FRUIT and FOOD) as well. Assume an ORM engine takes care after this as keeping such consistency would be very complex.

Let's also assume each of the tables in the hierarchy ends with a certain word (let's say FOOD: FRUIT_FOOD, CYTRUS_FOOD etc.) - I didn't include it to the chart above for sake of clarity.

Question: I have found a record in FOOD table with ID = 123 based on certain criteria. Thanks to the hierarchical structure, how do I find what tables contain the record with the very same ID using SQL only? I.e. my goal is to find out what * the lowest type in the hierarchy* the certain ID is related to.

Note: If you have also an answer for a newer version of Oracle, don't hesitate to include it as long as others might find it useful.

Nikolas Charalambidis
  • 40,893
  • 16
  • 117
  • 183

1 Answers1

1

Assuming all these tables have a column ID but you may adjust based on the example.

Q1. what tables contain the record with the very same ID using SQL only

You could use a series unions to determine this eg.

SELECT 
    id, 
    table_type,
    heirarchy_level
FROM (
    SELECT ID, 'FOOD', 1 FROM FOOD
    UNION ALL
    SELECT ID,'FRUIT',2 FROM FRUIT
    UNION ALL
    SELECT ID,'CYTRUS',3 FROM CYTRUS
    UNION ALL
    SELECT ID,'ORANGE',4 FROM ORANGE
    UNION ALL
    SELECT ID,'GREPFRUIT',4 FROM GREPFRUIT
    UNION ALL
    SELECT ID,'VEGETABLE',2 FROM VEGETABLE
    UNION ALL
    SELECT ID,'MEAT',2 FROM MEAT
    UNION ALL
    SELECT ID,'BEEF',3 FROM BEEF
    UNION ALL
    SELECT ID,'SIRLOIN',4 FROM SIRLOIN
    UNION ALL
    SELECT ID,'RIBEYE',4 FROM RIBEYE
    UNION ALL
    SELECT ID,'CHICKEN',3 FROM CHICKEN
) t
WHERE 
    id = 123

This would return a table with the id=123 but more importantly a table listing all tables where the record was present along with the depth/level in the hierarchy. You could then use MAX or order by to determine the deepest level

Q2. what is the lowest type in the hierarchy the certain ID is related to

This would return only one record with the lowest type

SELECT 
    id, 
    table_type,
    heirarchy_level
FROM (
    SELECT ID, 'FOOD', 1 FROM FOOD
    UNION ALL
    SELECT ID,'FRUIT',2 FROM FRUIT
    UNION ALL
    SELECT ID,'CYTRUS',3 FROM CYTRUS
    UNION ALL
    SELECT ID,'ORANGE',4 FROM ORANGE
    UNION ALL
    SELECT ID,'GREPFRUIT',4 FROM GREPFRUIT
    UNION ALL
    SELECT ID,'VEGETABLE',2 FROM VEGETABLE
    UNION ALL
    SELECT ID,'MEAT',2 FROM MEAT
    UNION ALL
    SELECT ID,'BEEF',3 FROM BEEF
    UNION ALL
    SELECT ID,'SIRLOIN',4 FROM SIRLOIN
    UNION ALL
    SELECT ID,'RIBEYE',4 FROM RIBEYE
    UNION ALL
    SELECT ID,'CHICKEN',3 FROM CHICKEN
) t
WHERE 
    id = 123
ORDER BY
    heirarchy_level desc
LIMIT 1
ggordon
  • 9,790
  • 2
  • 14
  • 27