How To Combine 2 SELECT Statements Using INNER JOIN Or UNION ALL-CodePudding

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

What i want to achieve for 1st user is like below

dose1_name	dose2_name
Moderna	Pfizer

Here is the 1st approach i took (using only 1 INNER JOIN)

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'

But it gives output like this

dose1_name	dose2_name
Moderna	Moderna

The 2nd approach i took (using INNER JOIN 2 times)

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'

But it gives no output

dose1_name	dose2_name
-	-

Any help will be appreciated.

CodePudding user response：

Your second approach of 2 joins is correct, however as a comment on your post mentioned, it should be the users table and then 2 left joins to the vaccine tables

For example:

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'

CodePudding user response：

An alternative is to use a correlated subquery for each required join:

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;

you may also need to coalesce the columns if both doses are not always populated.