如何在SQL中关联两个引用同一张表的外键以创建视图？

• 语法错误1：SELECT子句中获取第二剂接种日期时，遗漏了vaccine_dose表的别名second，错误写作second_vaccine_dose_date。该字段存储在vaccine_dose表中，不是vaccine表的字段，正确写法为second.vaccine_dose_date。
• 语法错误2：user是SQL保留关键字，直接用作表名会触发语法解析异常，需要用反引号包裹规避问题。
• 逻辑优化：调整连接顺序以user表为驱动表，更贴合查询用户接种信息的需求，减少非必要的空值行。

修改后的代码如下：

alter view user_view
as 
select `user`.user_id, `user`.first_name, `user`.last_name, `user`.middle_name,
    `user`.gender, `user`.phone_number, `user`.birthday, `user`.home_address,
    city.city_name, region.region_name,
    first_vaccine.vaccine_name as 'first_dose', first.vaccine_dose_date as 'first_dose_date',
    second_vaccine.vaccine_name as 'second_dose', second.vaccine_dose_date as 'second_dose_date'
from `user`
left join city on `user`.city_id = city.city_id
left join region on city.region_id = region.region_id
left join vaccine_dose as first on first.vaccine_dose_id = `user`.first_dose_id 
left join vaccine_dose as second on second.vaccine_dose_id = `user`.second_dose_id 
left join vaccine as first_vaccine on first.vaccine_id = first_vaccine.vaccine_id
left join vaccine as second_vaccine on second.vaccine_id = second_vaccine.vaccine_id;

如果修改后仍存在第二剂数据为空的情况，可单独查询user表的second_dose_id字段，确认存储的值是否在vaccine_dose表中存在对应合法记录。

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