dear Yii master,
I’m having a problem with mysql query, this query shall give result of people list, even though those people aren’t active anymore as employees…
But when some of them still active as employees, this query will also give results of the position of those people.
Well here is my code
$qtxt = "select * from
(select t.id, t.nama, t.gender, t.alamat, t.tempat_lahir, t.tgl_lahir, t.pendidikan_terakhir, t.no_hp, t.username, t.authority
from r_ms_perangkat_desa t where nama like '%$name%' ) per left join
(select t.id, t.nama, t.gender, t.alamat, t.tempat_lahir, t.tgl_lahir, t.pendidikan_terakhir, t.no_hp, t.username, t.authority
, j.id as j_id, j.nomor as nomor, j.tgl_keputusan as tgl_keputusan, j.nrpd
, j.ms_keputusan_oleh_id as ms_keputusan_oleh_id, ko.kode as ko_kode
, j.tgl_pelantikan_jabatan as tgl_pelantikan_jabatan, j.tgl_habis_jabatan as tgl_habis_jabatan, j.masa_jabatan_ke as masa_jabatan_ke
, j.ms_jabatan_id as ms_jabatan_id, mj.kode as mj_kode
, j.ms_sotk_desa_id as ms_sotk_desa_id, sotk.kode as sotk_kode
, j.ms_kecamatan_id as ms_kecamatan_id, k.kode_desa as k_kode
, j.ms_desa_id as ms_desa_id, d.kode_desa as d_kode
, j.ms_dasar_pengangkatan_id as ms_dasar_pengangkatan_id, j.aktif as j_aktif, j.keterangan
from r_ms_perangkat_desa t
inner join r_jabatan j on j.ms_perangkat_desa_id = t.id
inner join r_ms_desa k on j.ms_kecamatan_id = k.id
inner join r_ms_desa d on j.ms_desa_id = d.id
inner join r_ms_sotk_desa sotk on j.ms_sotk_desa_id = sotk.id
inner join r_ms_dasar_pengangkatan dp on j.ms_dasar_pengangkatan_id = dp.id
inner join r_ms_keputusan_oleh ko on j.ms_keputusan_oleh_id = ko.id
inner join r_ms_jabatan mj on j.ms_jabatan_id = mj.id
where t.nama like '%$name%' and j.aktif = 1) jab on per.id = jab.id
group by per.id limit 5";
$command = Yii::app()->db->createCommand($qtxt);
$result = $command->queryAll();
this query works fine and gives the result I wanted when I executed it in mysql-workbench, but not if I execute it using my Yii application.
The problem is it only gives result of this query
"select t.id, t.nama, t.gender, t.alamat, t.tempat_lahir, t.tgl_lahir, t.pendidikan_terakhir, t.no_hp, t.username, t.authority
, j.id as j_id, j.nomor as nomor, j.tgl_keputusan as tgl_keputusan, j.nrpd
, j.ms_keputusan_oleh_id as ms_keputusan_oleh_id, ko.kode as ko_kode
, j.tgl_pelantikan_jabatan as tgl_pelantikan_jabatan, j.tgl_habis_jabatan as tgl_habis_jabatan, j.masa_jabatan_ke as masa_jabatan_ke
, j.ms_jabatan_id as ms_jabatan_id, mj.kode as mj_kode
, j.ms_sotk_desa_id as ms_sotk_desa_id, sotk.kode as sotk_kode
, j.ms_kecamatan_id as ms_kecamatan_id, k.kode_desa as k_kode
, j.ms_desa_id as ms_desa_id, d.kode_desa as d_kode
, j.ms_dasar_pengangkatan_id as ms_dasar_pengangkatan_id, j.aktif as j_aktif, j.keterangan
from r_ms_perangkat_desa t
inner join r_jabatan j on j.ms_perangkat_desa_id = t.id
inner join r_ms_desa k on j.ms_kecamatan_id = k.id
inner join r_ms_desa d on j.ms_desa_id = d.id
inner join r_ms_sotk_desa sotk on j.ms_sotk_desa_id = sotk.id
inner join r_ms_dasar_pengangkatan dp on j.ms_dasar_pengangkatan_id = dp.id
inner join r_ms_keputusan_oleh ko on j.ms_keputusan_oleh_id = ko.id
inner join r_ms_jabatan mj on j.ms_jabatan_id = mj.id
where t.nama like '%$name%' and j.aktif = 1"
but not this one
"select t.id, t.nama, t.gender, t.alamat, t.tempat_lahir, t.tgl_lahir, t.pendidikan_terakhir, t.no_hp, t.username, t.authority
from r_ms_perangkat_desa t where nama like '%$name%'"
anyone, please help me with this query, what can I do in order to get the result I wanted… 
thank you very much…
*I’m sorry for my bad English
