CODEWITHSUNDEEP
mysql
HealMee
HealMee

Reputation: 85

MySQL not returning null column joined value

I have these 4 tables:
tables

My expected result:
expected result

this my current query (helped from 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

this the result from the query:
enter image description here

how do i get the result that im expecting for? I'm really unexperienced in advanced query like this, any help is very much apreciated. Thank you.

Upvotes: 0

Views: 25

Answers (1)

Sandesh
Sandesh

Reputation: 1044

Hope this helps.

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

Upvotes: 1

Related Questions