SELECT DISTINCT

    TRIM(nasabah.NASABAH_ID) AS cif
    ,CASE
        WHEN nasabah.TGL_BUKA = '0000-00-00' THEN '9999-12-31'
        ELSE nasabah.TGL_BUKA
    END AS tanggal_registrasi_cif
    ,TRIM(nasabah.NAMA_NASABAH) AS nama_nasabah
	,CASE
		WHEN nasabah.JENIS_NASABAH='1' THEN 'Individu'
		ELSE 'Non Invidividu'
	END AS jenis_nasabah
    ,TRIM(nasabah.ALAMAT_DOMISILI) AS alamat
    ,TRIM(nasabah.KOTA) AS kota
    ,TRIM(nasabah.NO_HP) AS no_handphone
    ,TRIM(nasabah.EMAIL) AS email
    ,TRIM(nasabah.no_id) AS nik
    ,nasabah.tgllahir AS tanggal_lahir
    ,TRIM(kodegroup1_nasabah.DESKRIPSI_GROUP1) AS agama
    ,TRIM(kodegroup2_nasabah.DESKRIPSI_GROUP2) AS nasabah_group2
    ,TRIM(kodegroup3_nasabah.DESKRIPSI_GROUP3) AS nasabah_group3
    ,TRIM(kodegroup4_nasabah.DESKRIPSI_GROUP4) AS nasabah_group4
	,TRIM(kodegroup5_nasabah.DESKRIPSI_GROUP5) AS nasabah_group5
    ,TRIM(kodegroup6_nasabah.DESKRIPSI_GROUP6) AS nasabah_group6
    ,TRIM(kodegroup7_nasabah.DESKRIPSI_GROUP7) AS nasabah_group7
    
FROM tabtrans
JOIN tabung ON tabtrans.NO_REKENING = tabung.NO_REKENING
JOIN nasabah ON tabung.NASABAH_ID = nasabah.nasabah_id
LEFT JOIN kodegroup1_nasabah ON nasabah.NASABAH_GROUP1 = kodegroup1_nasabah.NASABAH_GROUP1
LEFT JOIN kodegroup2_nasabah ON nasabah.NASABAH_GROUP2 = kodegroup2_nasabah.NASABAH_GROUP2
LEFT JOIN kodegroup3_nasabah ON nasabah.NASABAH_GROUP3 = kodegroup3_nasabah.NASABAH_GROUP3
LEFT JOIN kodegroup4_nasabah ON nasabah.NASABAH_GROUP4 = kodegroup4_nasabah.NASABAH_GROUP4
LEFT JOIN kodegroup5_nasabah ON nasabah.NASABAH_GROUP5 = kodegroup5_nasabah.NASABAH_GROUP5
LEFT JOIN kodegroup6_nasabah ON nasabah.NASABAH_GROUP6 = kodegroup6_nasabah.NASABAH_GROUP6
LEFT JOIN kodegroup7_nasabah ON nasabah.NASABAH_GROUP7 = kodegroup7_nasabah.NASABAH_GROUP7
