如何在单条SQL查询中使用两次LEFT JOIN获取午餐和晚餐餐食名称

涉及表结构 #

menu表（用餐记录表） #

字段如下：

• userID：用户ID
• date：用餐日期
• lunch：午餐对应foods表的食物ID
• dinner：晚餐对应foods表的食物ID

foods表（食物字典表） #

字段如下：

• id：食物唯一主键
• food_name：食物名称

原有查询的问题 #

原有语句仅将lunch字段与foods表ID做关联，仅能返回午餐对应的食物名称，无法匹配晚餐ID对应的名称：

SELECT m.user_id,
       m.date,
       m.lunch,
       m.dinner,
       f.id,
       f.food_name
FROM 
       menu m 
LEFT JOIN foods f ON
       m.lunch = f.id;

修正方案 #

对foods表做两次左连接，分别用不同别名区分午餐、晚餐的关联关系，即可同时返回两餐对应的食物名称，修正后语句：

SELECT
  m.userID,
  m.date,
  m.lunch,
  fl.food_name AS lunch_food_name,
  m.dinner,
  fd.food_name AS dinner_food_name
FROM menu m
LEFT JOIN foods fl
  ON m.lunch = fl.id
LEFT JOIN foods fd
  ON m.dinner = fd.id;

注意点 #

• 两次关联foods表必须起不同别名，不然数据库会报表别名冲突错误
• 别名不用固定按示例写，只要能区分开两次关联的用途即可
• 保留LEFT JOIN是为了兼容某一餐没填食物ID的情况，不会把没填全的用餐记录筛掉

内容的提问来源于stack exchange，提问作者Sadegh