title: Join on tables (SQL)
body: <p>I am a newbie to SQL and i have a question here. I am working in Postgresql. 
  My tables look like </p>
  
  <p><strong>change</strong></p>
  
  <pre><code>     id action_id(fk)   field_id(fk)    old_value   new_value   
  ------------------------------------------------------------------
        39      15              14                           testPool
        40      15              15                           testSystem 
        41      15              16                           61019
  </code></pre>
  
  <p><strong>action</strong></p>
  
  <pre><code>     id  description       audited_table     audited_row      audited_type  
  -----------------------------------------------------------------------------
       15  Added system       systemtable        61019          insert
  </code></pre>
  
  <p><strong>field</strong></p>
  
  <pre><code>      id       audited_table                name    
  ---------------------------------------------------------------------------
        14       systemtable                 pool
        15       systemtable                 storagesystem
        15       systemtable                 id
  </code></pre>
  
  <p>I would want to write a query to get the following view</p>
  
  <pre><code>  id        description                  audited_table               audited_row      audited_type   field.name1     name1-&gt;new_value  field.name2     name2-&gt;new_value
  -----------------------------------------------------------------------------------------------------------------------------------------------------------
    15        Added system                 systemtable                 61019              insert         pool            testPool        storagesystem    testSystem
  </code></pre>
  
  <p>Basically i want to flatten(rows into columns) the 'change' table and join it with 'action' table. Please note that <strong>'Change'</strong> table <strong>references to 'action' and 'field'</strong> tables </p>
  
  <p>Schema diagram looks like below</p>
  
  <p>Any help is appreciated, Thanks</p>
  
  <p><img src="https://i.stack.imgur.com/FCEpo.jpg" alt="enter image description here"></p>