title: SQLite select multiple foreign keys to one row
body: <p>I'm trying to set up a high scores board for a game I'm making. On the board, I'm using foreign keys to two other boards, players and weapons. Each score stores the four weapons the player used on that run. The tables are set up like this:</p>
  
  <pre><code>Scores
  id|playerid|score|weapon0id|weapon1id|weapon2id|weapon3id
  
  Players
  id|name
  
  Weapons
  id|name
  </code></pre>
  
  <p>I want to select multiple rows from the scores table with ids replaced by the appropriate names. I'm able to get the correct player name and one weapon using this statement:</p>
  
  <pre><code>SELECT scoreID, Players.playerName, scoreVal,
      Weapons.weaponLabel, scoreW1, scoreW2, scoreW3
  FROM Scores, Players, Weapons
  WHERE Players.playerID = scorePlayer AND Weapons.weaponID = scoreW0
  </code></pre>
  
  <p>Everywhere I've looked shows that to be the best way to get a value from a row referred to by a foreign key. It works fine for the player name, but there seems to be no way to expand this to fill in multiple weapon names at once. Using an OR with the remaining weapons or using weaponID IN (w0,w1,w2,w3) seems to get one row for each weapon, not one row with each weapon in the appropriate spot.</p>
  
  <p>Is there any way to get the correct weapon names just using the select statement? Or will I need to have extra code loop through and replace each weapon id with the correct name?</p>