Reputation: 1850
I've got 2 tables - dishes and ingredients:
in Dishes, I've got a list of pizza dishes, ordered as such:
In Ingredients, I've got a list of all the different ingredients for all the dishes, ordered as such:
I want to be able to list all the names of all the ingredients of each dish alongside each dish's name.
I've written this query that does not replace the ingredient ids with names as it should, instead opting to return an empty set - please explain what it that I'm doing wrong:
SELECT dishes.name, ingredients.name, ingredients.id
FROM dishes
INNER JOIN ingredients
ON dishes.ingredient_1=ingredients.id,dishes.ingredient_2=ingredients.id,dishes.ingredient_3=ingredients.id,dishes.ingredient_4=ingredients.id,dishes.ingredient_5=ingredients.id,dishes.ingredient_6=ingredients.id, dishes.ingredient_7=ingredients.id,dishes.ingredient_8=ingredients.id;
It would be great if you could refer to:
Disregard The Text In Hebrew - Treat It As Your Own Language.
Upvotes: 1
Views: 2001
Reputation: 180
The reason you are getting an empty result is because you are setting a join condition that never gets satisfied. During the INNER join execution the database engine compares each record of the first table with each record of the second one trying to find a match where the id of the ingredient table record being evaluated is equal to ingredient1 AND ingredient2 AND so on. It would return some result if you create a record in the first table with the same ingredient in all 8 columns (testing purposes only).
Regarding the database structure, you choose a denormalized one creating 8 columns for each ingredient. There are a lot of considerations possible on this data structure (performance, maintainability, or just think if you are asked to insert a dish with 9 ingredients for example) and I would personally go for a normalized data structure instead.
But if you want to keep this, you should write something like:
SELECT dishes.name, ingredients1.name, ingredients1.id, ingredients2.name, ingredients2.id, ...
FROM dishes
LEFT JOIN ingredients AS ingredients1 ON dishes.ingredient_1=ingredients1.id
LEFT JOIN ingredients AS ingredients2 ON dishes.ingredient_2=ingredients2.id
LEFT JOIN ingredients AS ingredients3 ON dishes.ingredient_3=ingredients3.id
...
The LEFT join is required to get a result for unmatched ingredients (0 value when no ingredient is set reading your example)
Upvotes: 1
Reputation: 9306
1. The logic of the DB structuring - am I doing it correctly?
Pizza
PizzaIngredients
Ingredients
Pizza
would have ID
, name
, and type
where ID
is the primary key.
PizzaIngredients
would have PizzaId
and IngredientId
(this is a many-many table where the primary key is a composite key of PizzaId
and IngredientID
)
Ingredients
has ID
and name
where ID
is the primary key.
2. List all the names of all the ingredients of each dish alongside each dish's name. Something like this in MySQL
(untested):
SELECT p.ID, p.name, GROUP_CONCAT(i.name) AS ingredients
FROM pizza p
INNER JOIN pizzaingredients pi ON p.ID = pi.PizzaID
INNER JOIN ingredients i ON pi.IngredientID = i.ID
GROUP BY p.id
3. If you've encountered such a problem before - one that requires a single-to-many relationship - how did you solved it in a way different than this, using PHP & MySQL?
Upvotes: 1
Reputation: 1842
It seems to me that a better Database Structure would have a Dishes_Ingredients_Rel table, rather than having a bunch of columns for Ingredients.
DISHES_INGREDIENTS_REL
DishesID
IngredientID
Then, you could just do a much simpler JOIN.
SELECT Ingredients.Name
FROM Dishes_Ingredients_Rel
INNER JOIN Ingredients
ON Dishes_Ingredients.IngredientID = Ingredients.IngredientID
WHERE Dishes_Ingredients_Rel.DishesID = @DishesID
Upvotes: 2