title: Oracle join a table using a variable field
body: <p>I'm trying to get the rent rate for which a cage is to be charged with for each month. Here's the logic behind how to get it:</p>
  
  <ol>
  <li>If the rate is defined in the <code>cage</code> table use that table's foreign key to link to the <code>rate</code> table.</li>
  <li>If the rate is defined in the <code>rack</code> table use that table's foreign key to link to the <code>rate</code> table.</li>
  <li>If the rate is defined in the <code>room</code> table use that table's foreign key to link to the <code>rate</code> table.</li>
  <li>If all of the above is <code>false</code> return <code>$0.00</code> (<em>free rent</em>).</li>
  </ol>
  
  <p>So here are the tables for you to visually understand what I currently have.</p>
  
  <h3>Cages</h3>
  
  <pre><code>id    cage_number     rate_id     rack_id    room_id
  ----------------------------------------------------
  1     FG-12           600         1          1
  2     FG-13           600         1          1
  3     FG-14           NULL        2          1
  4     FG-15           NULL        2          3
  </code></pre>
  
  <h3>Racks</h3>
  
  <pre><code>id    rack_number     rate_id
  -----------------------------
  1     BN-806          700
  2     BN-971          NULL
  </code></pre>
  
  <h3>Rooms</h3>
  
  <pre><code>id    room_number     rate_id
  -----------------------------
  1     UM-100          800
  2     UM-150          900
  3     UM-200          NULL
  </code></pre>
  
  <h3>Rates</h3>
  
  <pre><code>id   rate_id    effective_on    rate_value
  ------------------------------------------
  1    600        01-Apr-2015     0.67
  2    600        01-Oct-2016     0.80
  3    700        01-Jan-2016     0.56
  4    800        01-Jul-2012     0.61
  5    800        01-Dec-2015     0.85
  6    900        01-Feb-2015     0.75
  7    900        01-Mar-2015     0.79
  8    900        01-Apr-2015     0.90
  </code></pre>
  
  <p>So as you can see there are multiple rows for each <code>rate_id</code> because the price changes from time to time. I already have a query to get the correct rate for each <code>rate_id</code> by checking the <code>effective_on</code> field:</p>
  
  <pre><code>SELECT *
  FROM rates r
  WHERE r.id = (
    SELECT MAX(rr.id) AS curr
    FROM rates rr
    WHERE rr.rent_id = r.rent_id
      AND TRUNC(rr.effective_on) &lt;= TRUNC(sysdate)
  )
  </code></pre>
  
  <p>Let's go back to what my main problem is. I need to join the <code>rate</code> table using the <code>rate_id</code> located on 3 different tables.</p>
  
  <h3>Current Solution</h3>
  
  <p>My current approach is to join the <code>rate</code> table 3 times using 3 different aliases but since there could be multiple rows for the rate, all 3 instances could have multiple rows each which results in a lot of un-needed rows.</p>
  
  <pre><code>FROM cages c
    LEFT JOIN racks rk ON rk.id = c.rack_id
    LEFT JOIN rooms rm ON rm.id = c.room_id
    LEFT JOIN rates cr ON cr.rate_id = c.rate_id
    LEFT JOIN rates rkr ON rkr.rate_id = rk.rate_id
    LEFT JOIN rates rmr ON rmr.rate_id = rm.rate_id
  </code></pre>
  
  <h3>Attempted (but miserably failed)</h3>
  
  <p>I was thinking of a solution where I would only need to join the <code>rate</code> table once to fix that problem but when I tried it - it failed (as expected).</p>
  
  <pre><code>FROM cages c
    LEFT JOIN racks rk ON rk.id = c.rack_id
    LEFT JOIN rooms rm ON rm.id = c.room_id
    CASE
      WHEN c.rate_id IS NOT NULL
        THEN LEFT JOIN rates r ON r.rate_id = c.rate_id
      WHEN rk.rate_id IS NOT NULL
        THEN LEFT JOIN rates r ON r.rate_id = rk.rate_id
      WHEN rm.rate_id IS NOT NULL
        THEN LEFT JOIN rates r ON r.rate_id = rm.rate_id
    END
  </code></pre>