SQLite query with multiple joins

Question

This is pretty much just a simple statemnet build that I believe in. I haven’t been working with SQL lately and seem to have forgotten how to do this. I have an element with several columns in it that reference another table for the name of this field. Like this

id, name, effect1, effect2,effect3,effect4 

Effects refer to another table in which there is only a column a and id and name. What I'm trying to do is run a query that pulls out these names for each of these effects.

sort of

 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 let's say I have an element with these values

 Toast, 1, 2, 3, 4 

and effects

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

I want him to show toasts, burnt, toasted, wet, texas

And ideas?

Update

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

In the column effect1, ... is the id number for the corresponding element in the effects table. Many elements are going to use the same effects, so instead of inflating this already large database with redundant data, I decided to use the connection to save space. At the same time, I managed to forget how, lol

Update # 2 This is an im im effect, but for 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 on 1, but if I try to do a few, it just works. Any ideas how I can get this effect for all 4 effects?

Answer 1

Each column requires a separate connection:

 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 

Do this with LEFT JOINs so that you don’t lose the line if any of the effects is not in the effects table.
In addition, this request depends on the uniqueness of effects.id .

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 

---

Regarding db location:

If each ingredient has 4 effects , your db design is perfect. If the number of effects varies or you have additional information for each effect, you can consider the n:m relationship between ingredients and effects , implemented by an additional table. (Replacing columns with four effects *). It might 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? ); 

A detailed manual contains detailed information .