SQLite query with multiple joins

Question

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:

id, name, effect1, effect2, effect3, effect4

The effects reference another table that only has a, id, and name columns. What I'm trying to do is run a query that will pull those names for each of those effects.

Something like:

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

So, say I have an item that has values like this:

Toast, 1, 2, 3, 4

and the effects are:

1, burned
2, untoasted
3, wet
4, texas 

I want it to display toast, burned, untoasted, wet, texas

And ideas?

update:

Table items
id, name, weight, value, effect1,effect2,effect3,effect4

Table effects
id, name

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

Update #2 This is the effect I'm going for, but on more than one of the effect columns

SELECT i.name, i.weight,i.value, e.name AS 'effect 1' 
FROM ingredients i JOIN effects e ON effects._id=i.effect1

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?

Answer 1

You need a distinct join for every column:

SELECT i.name
      ,i.weight
      ,i.value
      ,e1.name AS effect1
      ,e2.name AS effect2
      ,e3.name AS effect3
      ,e4.name AS effect4
FROM   ingredients i 
LEFT   JOIN effects e1 ON e1.id = i.effect1
LEFT   JOIN effects e2 ON e2.id = i.effect2
LEFT   JOIN effects e3 ON e3.id = i.effect3
LEFT   JOIN effects e4 ON e4.id = i.effect4

Make that LEFT JOINs so you don't lose the row if any of the effects is missing in the effects table.
Also, this query depends upon effects.id being unique.

Here is another way to do the same:

SELECT i.name
      ,i.weight
      ,i.value
      ,(SELECT e.name FROM effects e WHERE e.id = i.effect1) AS effect1
      ,(SELECT e.name FROM effects e WHERE e.id = i.effect2) AS effect2
      ,(SELECT e.name FROM effects e WHERE e.id = i.effect3) AS effect3
      ,(SELECT e.name FROM effects e WHERE e.id = i.effect4) AS effect4
FROM   ingredients i

---

Concerning the db layout:

If every ingredient has 4 effects your db design is fine. If the number of effects vary or you have additional information per effect, you might consider an n:m relationship between ingredients and effects, implemented by an additional table. (Replacing the four effect* columns.) Could look like this:

CREATE TABLE ingredients_effects
(
  ingredients_id integer references ingredients(id)
 ,effects_id     integer references effects(id)
-- more attributes like quantity or notes?
);

The fine manual has the details.