balloon balloon - 7 months ago 10
SQL Question

How do you join several columns from one entity to single column in a separate entity?

I'm trying to make a meal scheduling database for a weight loss clinic's customers. These are some recipes:

+-----------+-----------------------+
| recipe_ID | recipe_name |
+-----------+-----------------------+
| 1 | Healthy Burritos |
| 2 | Stuffed Peppers |
| 3 | Breakfast Pizza Slice |
+-----------+-----------------------+


This is a plan for a day:

+--------+-----------+-------+--------+
| ddp_ID | breakfast | lunch | dinner |
+--------+-----------+-------+--------+
| 1 | 3 | 1 | 2 |
+--------+-----------+-------+--------+


Each number in breakfast, lunch and dinner in a plan is a foreign key referring to a recipe's ID. I want to perform a query that will get the names of each recipe to display for the user. This is what I've got, but I'm getting the following error that I can't seem to work around: ERROR 1066 (42000): Not unique table/alias: 'daily_diet_plan'

SELECT
recipe.recipe_name
FROM
recipe
INNER JOIN daily_diet_plan ON (daily_diet_plan.breakfast = recipe.recipe_ID)
INNER JOIN daily_diet_plan ON (daily_diet_plan.lunch = recipe.recipe_ID)
INNER JOIN daily_diet_plan ON (daily_diet_plan.dinner = recipe.recipe_ID);


Thank you so much for any help you can provide!! I really appreciate it.

EDIT: This works!

SELECT
recipe.recipe_name
FROM
recipe
LEFT JOIN daily_diet_plan as brkfast ON (brkfast.breakfast = recipe.recipe_ID)
LEFT JOIN daily_diet_plan as lunch ON (lunch.lunch = recipe.recipe_ID)
LEFT JOIN daily_diet_plan as dinner ON (dinner.dinner = recipe.recipe_ID);

Answer

Update your query(I have added diff alias:

SELECT 
recipe.recipe_name 
FROM 
recipe 
INNER JOIN daily_diet_plan as brkfast ON (brkfast.breakfast = recipe.recipe_ID)
INNER JOIN daily_diet_plan as lunch ON (lunch.lunch = recipe.recipe_ID) 
INNER JOIN daily_diet_plan as dinner ON (dinner.dinner = recipe.recipe_ID);
Comments