MySQL 不返回空列连接值

Question

我有这 4 个表:


我的预期结果:

这是我当前的查询(来自 here 的帮助)

select a.No_Registrasi, a.Nama_CTKI, b.Nama_Negara, CONCAT('RA-', MAX(substring(c.ID_Rad, 4) + 0)) AS ID_Rad, CONCAT('Lab-',MAX(substring(d.ID_Lab, 5) + 0)) AS Id_Lab
FROM tb_registrasi a 
JOIN tb_negara_tujuan b
    ON a.ID_Negara = b.ID_Negara
JOIN tb_radiologi c
   ON a.No_Registrasi = c.No_Registrasi
JOIN tb_laboratorium d
   ON a.No_Registrasi = d.No_Registrasi
GROUP BY a.No_Registrasi, a.Nama_CTKI, b.Nama_Negara

这是查询的结果:

如何获得我期望的结果？我对这样的高级查询真的没有经验，非常感谢任何帮助。谢谢。

Answer 1

希望这有帮助。

select a.No_Registrasi, a.Nama_CTKI, b.Nama_Negara, CONCAT('RA-', MAX(substring(c.ID_Rad, 4) + 0)) AS ID_Rad, CONCAT('Lab-',MAX(substring(d.ID_Lab, 5) + 0)) AS Id_Lab
FROM tb_registrasi a 
LEFT JOIN tb_negara_tujuan b
    ON a.ID_Negara = b.ID_Negara
LEFT JOIN tb_radiologi c
   ON a.No_Registrasi = c.No_Registrasi
LEFT JOIN tb_laboratorium d
   ON a.No_Registrasi = d.No_Registrasi
GROUP BY a.No_Registrasi, a.Nama_CTKI, b.Nama_Negara