SQLite query with multiple joins

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 effect4FROM   ingredients i LEFT   JOIN effects e1 ON e1.id = i.effect1LEFT   JOIN effects e2 ON e2.id = i.effect2LEFT   JOIN effects e3 ON e3.id = i.effect3LEFT   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 effect4FROM   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.