title: SQL complicated join query
body: <p>I have two tables: <code>match(id.match, date, home, away)</code> and <code>team(id. team, name)</code>.  In <code>match</code> <code>home</code> and <code>away</code> are foreign keys to the <code>team id</code>. In my query, I want an output record with <code>date</code> of <code>match,</code> and <code>name</code> of these two teams. </p>
  
  <p>I have tried:</p>
  
  <pre><code>SELECT m.date, m.home, m.away, t.name 
  FROM `match` m
  JOIN team t ON m.home = t.id_team 
  ORDER BY m.date
  </code></pre>
  
  <p>But this outputs two records instead of one. Is it possible to do what I want with sql or I should just change the table's design? </p>