title: Multiple joins to get the same lookup column for different values
body: <p>We have a rather large SQL query, which is rather poorly performing. One of the problems (from analysing query plan) is the number of joins we have.</p>
  
  <p>Essentially we have values in our data that we need to do a look up on another table.to get the value to display to the user. The problem is that we have do a join on the same table 4 times because there are 4 different columns that all need the same look up.</p>
  
  <p>Hopefully this diagram might make it clearer</p>
  
  <pre><code>Raw_Event_data
  event_id,   datetime_id,         lookup_1, lookup_2, lookup_3, lookup_4
  1,          2013-01-01_12:00,    1,        5,        3,         9          
  2,          2013-01-01_12:00,    121,      5,        8,         19
  3,          2013-01-01_12:00,    11,       2,        3,         32
  4,          2013-01-01_12:00,    15,       2,        1,         0
  
  Lookup_table
  lookup_id,      lookup_desc
  1,              desc1
  2,              desc2
  3,              desc3
  ...
  </code></pre>
  
  <p>Our query then looks something like this</p>
  
  <pre><code>Select 
      raw.event_id, 
      raw.datetime_id,
      lookup1.lookup_desc,
      lookup2.lookup_desc,
      lookup3.lookup_desc,
      lookup4.lookup_desc,
  FROM 
      Raw_Event_data raw, Lookup_table lookup1,Lookup_table lookup2,Lookup_table lookup3,Lookup_table lookup4
  WHERE raw.event_id = 1 AND
  raw.lookup_1 *= lookup1 AND
  raw.lookup_2 *= lookup2.lookup_id AND
  raw.lookup_3 *= lookup3.lookup_id AND
  raw.lookup_4 *= lookup4.lookup_id
  </code></pre>
  
  <p>So I get as an output</p>
  
  <pre><code> 1, 2013-01-01_12:00, desc1, desc5, desc3, desc9
  </code></pre>
  
  <p>As I said the query works, but the joins are killing the performance.
  That is a simplistic example I give there, in reality there will be 12 joins like above and we won't be selecting a specific event, but rather a range of events.</p>
  
  <p>The question is, is there a better way of doing those joins.</p>