title: How to find table names having ID (primary key) of a certain value in a hierarchy of tables?
body: <p>I use Oracle 11g and have a massive number of tables representing inheritance, where a base parent table has a primary key <code>NUMBER ID</code>. The subsequent tables inherit from it, representing through the shared primary key <code>NUMBER ID</code>. Let's assume there is a multiple layers of such inheritance.</p>
  <p>To have a clear picture, let's work with the following simplified structure and assume the hierarchy is quite complex:</p>
  <pre><code>- TABLE FOOD
    - TABLE FRUIT
      - TABLE CYTRUS
        - TABLE ORANGE
        - TABLE GREPFRUIT
    - TABLE VEGETABLE
    - TABLE MEAT
      - TABLE BEEF
        - TABLE SIRLOIN
        - TABLE RIB EYE
      - TABLE CHICKEN
  </code></pre>
  <p>This is not taxative, regardless of how dumb the example is, assume such a multi-layered hierarchy using <a href="https://stackoverflow.com/a/3579462/3764965">Class Table Inheritance (aka Table Per Type Inheritance)</a>.</p>
  <p>If you want to insert a record to a table <code>ORANGE</code> having a certain generated <code>ID</code>, there must be inserted records to the parent tables (<code>CYTRUS</code>, <code>FRUIT</code> and <code>FOOD</code>) as well. Assume an ORM engine takes care after this as keeping such consistency would be very complex.</p>
  <p>Let's also assume each of the tables in the hierarchy ends with a certain word (let's say <code>FOOD</code>: <code>FRUIT_FOOD</code>, <code>CYTRUS_FOOD</code> etc.) - I didn't include it to the chart above for sake of clarity.</p>
  <p><strong>Question</strong>: I have found a record in <code>FOOD</code> table with <code>ID = 123</code> 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 <code>ID</code> is related to.</p>
  <p><strong>Note:</strong> 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.</p>