如何使用内连接或联合所有来组合两个SELECT语句

Question

下面有一个名为user和vaccine的表

用户

second_dosePF88AZ51

Answer 1

你的第二种连接方法是正确的，但是正如你帖子上提到的评论，它应该是用户表，然后2个左连接到疫苗表

例如:

SELECT vac1.vaccine_name as dose1_name, vac2.vaccine_name as dose2_name
FROM user
LEFT JOIN vaccine as vac1 ON user.first_dose = vac1.vaccine_id
LEFT JOIN vaccine as vac2 ON user.second_dose = vac2.vaccine_id
WHERE user.user_id = 'XXXX'

Answer 2

另一种选择是对每个所需的连接使用相关子查询:

select User_Id,
(select vaccine_name from vaccine v where v.vaccine_id = u.first_dose) as dose1_name,
(select vaccine_name from vaccine v where v.vaccine_id = u.second_dose) as dose2_name
from user u;

如果两个剂量并不总是被填充，您可能还需要合并列。