title: Replacing select values with values from other tables
body: <p>I am trying to query upon a table, where I want to replace it's foreign key with a value the related table has a fk constraint to. I don't know how to explain it better in words - I have made a minimal example to demonstrate my difficulty.</p>
  
  <pre class="lang-sql prettyprint-override"><code>    create table customers (
          cpr VARCHAR(10) PRIMARY KEY,
          name VARCHAR(100) NOT NULL
      );
  
      CREATE TABLE accounts (
          id SERIAL PRIMARY KEY,
          balance INT DEFAULT 0,
          customerCpr VARCHAR(10) references customers (cpr) NOT NULL,
      );
  
      CREATE TABLE transactions(
          id SERIAL PRIMARY KEY,
          retriever INT references accounts (id) NOT NULL,
          giver     INT references accounts (id) NOT NULL,
          timestamp timestamp default current_timestamp,
          amount    INT NOT NULL
      );
  </code></pre>
  
  <p>So I want to query something like </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT retrieverCustomer.name, giverCustomer.name, tran.timestamp tran.amount 
  FROM transactions tran 
  WHERE tran.retriever = 20 OR tran.giver = 20...
  </code></pre>
  
  <p>I want it to return this format: </p>
  
  <pre><code> id | retName   | gName |         timestamp          | amount 
  ----+-----------+-------+----------------------------+--------
    1 | Elisabeth | Jonas | 2020-05-09 11:07:50.614155 |   1500
    2 |  Veronica | Peter | 2020-05-09 11:07:50.614155 |   2200
    3 |Kristoffer | Jens  | 2020-05-09 11:07:50.614155 |   1700
  </code></pre>
  
  <p>I am not sure how to achieve this, I have tried with some joining and some conditionals but I fail to see the problem through. If someone know how to achieve this or can explain why it is not ideal to do so it would really help me out.</p>