SELECT
                                    IF(a.gabung = 0, a.hari,
                                        (SELECT hari FROM tbl_jadwal_matkul WHERE kd_jadwal = a.referensi)) AS hari,
                                    IF(a.gabung = 0, a.waktu_selesai,
                                        (SELECT waktu_selesai FROM tbl_jadwal_matkul WHERE kd_jadwal = a.referensi)) AS waktu_selesai,
                                    IF(a.gabung = 0, a.waktu_mulai,
                                        (SELECT waktu_mulai FROM tbl_jadwal_matkul WHERE kd_jadwal = a.referensi)) AS waktu_mulai,
                                    IF(a.gabung = 0, kd_matakuliah,
                                        (SELECT kd_matakuliah FROM tbl_jadwal_matkul WHERE kd_jadwal = a.referensi)) AS kd_matakuliah,
                                    IF(a.gabung = 0, a.kelas,
                                        (SELECT kelas FROM tbl_jadwal_matkul WHERE kd_jadwal = a.referensi)) AS kelas,
                                    IF(a.gabung = 0, b.kuota,
                                        (SELECT r.kuota FROM tbl_ruangan r JOIN tbl_jadwal_matkul j ON r.id_ruangan = j.kd_ruangan WHERE j.kd_jadwal = a.referensi)) AS kuota,
                                    IF(a.gabung = 0, b.ruangan,
                                        (SELECT r.ruangan FROM tbl_ruangan r JOIN tbl_jadwal_matkul j ON r.id_ruangan = j.kd_ruangan WHERE j.kd_jadwal = a.referensi)) AS ruangan,
                                    IF(a.gabung = 0, b.kode_ruangan,
                                        (SELECT r.kode_ruangan FROM tbl_ruangan r JOIN tbl_jadwal_matkul j ON r.id_ruangan = j.kd_ruangan WHERE kd_jadwal = a.referensi)) AS kode_ruangan,
                                    IF(a.gabung = 0, c.nama,
                                        (SELECT k.nama FROM tbl_karyawan k JOIN tbl_jadwal_matkul j ON k.nid = j.kd_dosen WHERE kd_jadwal = a.referensi)) AS nama,
                                    IF(a.gabung <> 0,a.`referensi`, a.`kd_jadwal`) AS kd_jadwal,
                                    (SELECT COUNT(DISTINCT z.npm_mahasiswa) FROM tbl_krs z
                                    WHERE z.kd_jadwal = IF(a.gabung <> 0,a.referensi,a.kd_jadwal)) AS jumlah
                                FROM tbl_jadwal_matkul a
                                LEFT JOIN tbl_ruangan b ON a.kd_ruangan = b.id_ruangan
                                LEFT JOIN tbl_karyawan c ON a.kd_dosen = c.nid
                                WHERE a.kd_matakuliah = 'MKWU-2002'
                                AND a.kd_jadwal LIKE '2/%'
                                AND a.kd_tahunajaran = '20192'
                                -- jika jadwal tersebut adalah jadwal induk maka ambil yang sesuai dengan kategori kelas mahasiswa (PG/SR/PK)
                                -- jika jadwal turunan, maka cek data jadwal induknya untuk melihat kategori kelas dari jadwal induk tsb
                                AND IF('PG' = 'PG',
                                        a.waktu_kelas = 'PG',
                                        a.waktu_kelas IN ('SR','PK'))
                                AND IF(SUBSTR(a.kd_matakuliah, 1,2) = 'MK',
                                        a.open = 1,
                                        a.open IS NULL)
                                AND IF(a.gabung = 0,
                            (SUBSTR(b.kuota,1,2) - (SELECT COUNT(DISTINCT z.npm_mahasiswa) FROM tbl_krs z WHERE z.kd_jadwal = a.kd_jadwal) ) > 0,
                            (SUBSTR((SELECT kuota FROM tbl_ruangan r JOIN tbl_jadwal_matkul j ON r.id_ruangan = j.kd_ruangan WHERE j.kd_jadwal = a.referensi),1,2) -
                            (SELECT COUNT(DISTINCT z.npm_mahasiswa) FROM tbl_krs z WHERE z.kd_jadwal = a.kd_jadwal) ) ) > 0