title: SQL: Join to different table based on column data
body: <p>My scenario are as below:</p>
  
  <p>Table: t_audit</p>
  
  <pre><code>row | colname | oldvalue | newvalue
  ===================================
  1   | locid   | 001      | 002
  2   | typeid  | 010      | 011
  </code></pre>
  
  <p>Table: t_ref_audit</p>
  
  <pre><code>colname | desc        | link_table  | link_key  | link_desc
  ===========================================================
  locid   | Location    | t_ref_loc   | locid     | loc_desc
  typeid  | Type        | t_ref_type  | typeid    | type_desc
  </code></pre>
  
  <p>Table: t_ref_loc</p>
  
  <pre><code>locid | type_desc
  ==================
  001   | LOCATION A
  002   | LOCATION B
  </code></pre>
  
  <p>Table: t_ref_type</p>
  
  <pre><code>typeid | loc_desc
  ==================
  010   | TYPE C
  011   | TYPE D
  </code></pre>
  
  <p>As you can see from above, the first table is my audit log table, and 2nd table is the reference table. 3rd and 4th tables are reference tables. By using simple SQL below I can get the proper description for the column name based on t_ref_audit table.</p>
  
  <pre><code>SELECT t_ref_audit.desc, t_audit.oldvalue, t_audit.newvalue
  FROM t_audit, t_ref_audit
  WHERE t_audit.colname = t_ref_audit.colname
  </code></pre>
  
  <p>My problem now is, the columns on t_audit.oldvalue and t_audit.newvalue contains reference code ID from other reference tables (t_ref_loc &amp; t_ref_type). I wanted to show the proper description based on columns from t_ref_audit.link_desc instead of just the ID as below:</p>
  
  <pre><code>coldesc  | oldvalue   | newvalue
  ==================================
  Location | LOCATION A | LOCATION B
  Type     | TYPE C     | TYPE D
  </code></pre>
  
  <p>Hope someone can enlighten me on this. Thank you.</p>