title: Two foreign keys in parent table reference same child table. How to structure INNER JOIN?
body: <p>For simplicity's sake, I'll describe my issue as a theoretical here.</p>
  
  <p>Imagine you have two tables-- MATCHES and FIGHTERS. 'Fighters' has a list of fighters (pk_fighter_id, fighter_name), any two of which can be scheduled to spar each other. 'Matches' is perhaps a three-field table (pk_fight_num, fk_fighter_id1, fk_fighter_id2) which keeps track of these pairings. Fighter1 and Fighter2 are foreign keys which reference entries in the Fighters table.</p>
  
  <p>I need to get a list of all fights that shows who is fighting who, i.e. "23123 | Pacquaio | Marquez." How on earth do I structure my query for this though?</p>
  
  <p>I'd imagine something like:</p>
  
  <pre><code>select fk_fighter_id1, fk_fighter_id2
  from matches
  inner join fighters on matches.fk_fighter_id1=fighters.pk_fighter_id
  inner join fighters on matches.fk_fighter_id2=fighters.pk_fighter_id;
  </code></pre>
  
  <p>When I tried mocking this up in Access I cobbled this together but it doesn't work:</p>
  
  <pre><code>SELECT matches.match_no, fighters.fighter_name, fighters.fighter_name
  FROM fighters 
  INNER JOIN matches ON (fighters.fighter_id = matches.fighter2) AND (fighters.fighter_id = matches.fighter1);
  </code></pre>
  
  <p>So, any thoughts? I just don't know where to go from here.</p>