如何使用内在或联合组合2个选择语句

Question

I have a table named user and vaccine like below

user

user_id	first_dose	second_dose
A221H	MN03	PF88
C221G	SV05	AZ51

vaccine

vaccine_id	vaccine_name
MN03	Moderna
SV05	Sputnik V
PF88	Pfizer
AZ51	Astrazeneca
SN12	Sinopharm
CNV7	Convidecia

我想为第一用户实现的目标是下面的

dose1_name	dose2_name
moderna	pfizer

这是我采用的第一个方法（仅使用1个内部加入），

SELECT vaccine_name AS dose1_name, vaccine_name AS dose2_name FROM vaccine INNER JOIN user ON first_dose = vaccine.vaccine_id WHERE user.user_id = 'A221H' 

一样的输出

但它给出了像dose1_name dose1_name	dose2_name
Moderna	Moderna

，我采用的第二种方法是我采用的第二种方法），

SELECT vac_name AS dose1_name, vac_name AS dose2_name FROM vaccine INNER JOIN user fd ON fd.first_dose = vaccine.vaccines_id INNER JOIN user sd ON sd.second_dose = vaccine.vaccines_id  WHERE fd.user_id = 'A221H' AND sd.user_id = 'A221H'

没有输出

dose1_name	dose2_name--
但它	将不胜

感激。

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;

如果两种剂量并不总是填充，则可能需要 cocce 。