title: SQL: How can I select two IDs as two names from the same table?
body: <p>I'm new to SQL and trying to get a handle on joins and referencing.</p>
  
  <p>Databases look like this:</p>
  
  <p><strong>Players</strong></p>
  
  <pre><code> id | name
  ----+-------
    1 | john
    2 | tim
    3 | frank
    4 | tony
    5 | mark
  </code></pre>
  
  <p><strong>Matches</strong></p>
  
  <pre><code> id | winner | loser
  ----+--------+-------
    1 |      2 |     4
    2 |      3 |     4
    3 |      4 |     5
    4 |      1 |     4
  </code></pre>
  
  <p>I want to get this result:</p>
  
  <pre><code> id | winner | loser
  ----+--------+-------
    1 |    tim |  tony
    2 |  frank |  tony
    3 |   tony |  mark
    4 |   john |  tony
  </code></pre>
  
  <p>So far I can do this:</p>
  
  <pre><code>select matches.id, players.name as winner, players.name as loser
    from matches, players 
    where winner = players.id;
  </code></pre>
  
  <p>But if I add <code>and loser = players.id</code>, it returns no rows.</p>
  
  <p>I've also tried this:</p>
  
  <pre><code>select matches.id, players.name as winner, players.name as loser
    from matches
    join players on matches.winner = players.id
    join players on matches.loser = players.id;
  </code></pre>
  
  <p>which produces <code>ERROR:  table name "players" specified more than once</code>.</p>
  
  <p>I have googled this for an hour and can find lots of cases that <em>almost</em> get at what I want, but I still can't figure out how to replace two IDs with two names from the same table.</p>