title: PostgreSQL: create temporary table, added to main select query and avoid more than one row error
body: <p>Again, I'm struggling to understand how SQL works. Hence, I'll explain to you what data I have and what I've done so far.</p>
  
  <p>I have 5 tables:</p>
  
  <p>loan</p>
  
  <p><a href="https://i.stack.imgur.com/bGFDC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bGFDC.png" alt="loan"></a></p>
  
  <p>loan_role</p>
  
  <p><a href="https://i.stack.imgur.com/CTu57.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CTu57.png" alt="loan role"></a></p>
  
  <p>legal_person</p>
  
  <p><a href="https://i.stack.imgur.com/DCP9q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DCP9q.png" alt="legal_person"></a></p>
  
  <p>legal_person_attribute</p>
  
  <p><a href="https://i.stack.imgur.com/7l65U.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7l65U.png" alt="legal_person_attribute"></a></p>
  
  <p>legal_person_attribute_key</p>
  
  <p><a href="https://i.stack.imgur.com/emDU3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/emDU3.png" alt="legal_person_attribute_key"></a></p>
  
  <p>I want to do a selection of different columns, where the attribute key selects the value column that I want to have. What I have done is this:</p>
  
  <pre><code>select l.id as loan_nr, l.country, l.currency, l.principal_amount, l.duration, l.nominal_interest_rate, l.annuity, l.payout_date                              ,
         (select lpa.value as company_name
          from etl.legal_person_attribute lpa, etl.legal_person_attribute_key lpak
          where lpa.fk_legal_person_attribute_key = lpak.id and lpak.attribute_key = 'companyName'
         )
  from etl.loan l
  where l.country ='DE'
  </code></pre>
  
  <p>However, it returns this error: </p>
  
  <blockquote>
    <p>ERROR: more than one row returned by a subquery used as an expression</p>
  </blockquote>
  
  <p>Trying it separately, the query works. </p>
  
  <pre><code>select lpa.value as company_name
  from etl.legal_person_attribute lpa, etl.legal_person_attribute_key lpak                            
  where lpa.fk_legal_person_attribute_key = lpak.id and lpak.attribute_key = 'companyName'    
  </code></pre>
  
  <p>How would you do it? Maybe using temporary tables (I don't know how to do it honestly)? Could it be a joining problem?</p>
  
  <p>Thank you in advance!</p>
  
  <p>More info:</p>
  
  <p>The images that I put are some examples of the tables that I’m working with, just to illustrate how they are.</p>
  
  <p>The query is incomple because I don’t know how to finish it.</p>
  
  <p>What I would like to have is a table with the main selected columns and then, adding subqueries, I want to retrieve a columb for each value.</p>
  
  <p>I've done that:</p>
  
  <pre><code>select l.id as loan_nr, l.country, l.currency, l.principal_amount, l.duration, l.nominal_interest_rate, l.annuity, l.payout_date, 
  (select lpa.value from etl.legal_person_attribute lpa, etl.legal_person_attribute_key lpak where lpa.fk_legal_person_attribute_key = lpak.id and lpak.id = '180'
  )as postcode
  from etl.loan l,
  etl.loan_person_role lpr,
  etl.legal_person lp,
  etl.legal_person_attribute lpa, 
  etl.legal_person_attribute_key lpak
  where l.id = lpr.loan_id and lp.id_user = lpr.fk_legal_person and lpa.fk_user = lp.id_user and lpa.fk_legal_person_attribute_key = lpak.id and 
  --lpak.id = '180'and
  l.profit_center='SME' and 
  l.country='DE' and 
  l.payout_date::DATE &lt;= (date_trunc('month', current_date)-'1sec'::interval)::DATE
  group by l.id, l.country, l.currency, l.principal_amount, l.duration, l.nominal_interest_rate, l.annuity, l.payout_date, lpa.value
  limit 100 
  </code></pre>
  
  <p>And still same problem. Could it be due to the fact that value column stored all data vertically and I want to extract it in different column, giving them different names according to the attribute key?</p>