mysql - 如何将一个实体中的多个列连接到单独实体中的单个列？

Question

我正在尝试为减肥诊所的客户创建一个膳食安排数据库。这些是一些食谱:

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

这是一天的计划:

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

计划中的早餐、午餐和晚餐中的每个数字都是引用菜谱 ID 的外键。我想执行一个查询，获取要向用户显示的每个食谱的名称。这就是我所得到的，但我收到以下错误，但我似乎无法解决该错误: 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);

非常感谢您提供的任何帮助!对此，我真的非常感激。

编辑:这有效!

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) order by recipe.recipe_name;

Answer 1

更新您的查询(我添加了 diff 别名:

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);