title: Oracle: Is it possible to choose which table to join based on a column value?
body: <p>First of all, this isn't my design, and I don't have the option of changing it. I've got three tables that define a relationship between action items and incidents and audits. The tables, in very simplified form, are set up like this:</p>
  
  <pre><code>ACTION_ITEMS
  -------
  ID    SOURCE_ID    SOURCE_TYPE
  1     12345        INC
  2     67890        AUD
  
  INCIDENTS
  -------
  ID
  12345
  
  AUDITS
  -------
  ID
  67890
  </code></pre>
  
  <p>The SOURCE_TYPE column indicates which of the other two tables is relevant to that row. It's simple enough to get the details for all the records by creating separate queries for the incident and audit action items, joining those to the appropriate tables, and then performing a union.</p>
  
  <p>What I'd like to know is if it's possible to conditionally join to a table based on the value of a column. In other words, something like this, but that actually works:</p>
  
  <pre><code>SELECT
     AI.*
    ,INC.*
    ,AUD.*
  FROM ACTION_ITEMS AI
  JOIN
    CASE AI.SOURCE_TYPE
      WHEN 'INC' THEN INCIDENTS INC ON (AI.SOURCE_ID = INCIDENTS.ID)
      WHEN 'AUD' THEN AUDITS AUD ON (AI.SOURCE_ID = AUDITS.ID)
    END;
  </code></pre>
  
  <p>**Edited to clarify that I'm seeking to retrieve data from all tables.</p>