title: SQLite query with multiple joins
body: <p>This is pretty much just a simple join statemnet I believe. I've not worked with SQL much lately and seem to have forgotten how to do this. What I have is an item with few columns in it that reference another table for the name of that field. Like this:</p>
  
  <pre><code>id, name, effect1, effect2, effect3, effect4
  </code></pre>
  
  <p>The effects reference another table that only has <code>a</code>, <code>id</code>, and <code>name</code> columns. What I'm trying to do is run a query that will pull those names for each of those effects.</p>
  
  <p>Something like: </p>
  
  <pre><code>SELECT i.name,e.name AS effect1, e.name AS effect2, e.name AS effect3, 
         e.name AS effect4 
  FROM item i, effects e 
  WHERE i.effect1 = e.name 
   AND i.effect2 = e.name 
   AND i.effect3 = e.name 
   AND i.effect4 = e.name
  </code></pre>
  
  <p>So, say I have an item that has values like this:</p>
  
  <pre><code>Toast, 1, 2, 3, 4
  </code></pre>
  
  <p>and the effects are:</p>
  
  <pre><code>1, burned
  2, untoasted
  3, wet
  4, texas 
  </code></pre>
  
  <p>I want it to display <code>toast, burned, untoasted, wet, texas</code></p>
  
  <p>And ideas?</p>
  
  <p>update:</p>
  
  <pre><code>Table items
  id, name, weight, value, effect1,effect2,effect3,effect4
  
  Table effects
  id, name
  </code></pre>
  
  <p>In the effect1,... columns are the id number for the corresponding item in the effect table. A lot ofitems are going to share the same effects, so instead of inflating this already large database with redundant data, I decided to use a join to save space. At the same time I managed how to forget to do it, lol</p>
  
  <p>Update #2 
  This is the effect I'm going for, but on more than one of the effect columns</p>
  
  <pre><code>SELECT i.name, i.weight,i.value, e.name AS 'effect 1' 
  FROM ingredients i JOIN effects e ON effects._id=i.effect1
  </code></pre>
  
  <p>This works for 1, but if I try to do multiple it just crashes. Any ideas how I can get that effect for all 4 effects?</p>