This is my model, which works when I query one table only:
function cari_username()
{
$username = $this->input->post('username');
$this->db->like('username',$username);
$data = $this->db->get('user');
return $data;
}
What I need is to join data from several tables, but now my code fails:
function cari_username()
{
$this->db->from('user');
$username = $this->input->post('username');
$data = $this->db->query("SELECT * FROM (SELECT user.id_user,mahasiswa.nama,user.password,user.username
FROM (`mahasiswa`)
LEFT JOIN `pendaftaran_anggota` ON `pendaftaran_anggota`.`nim` = `mahasiswa`.`nim`
LEFT JOIN `anggota` ON `pendaftaran_anggota`.`id_anggota` = `anggota`.`id_anggota`
LEFT JOIN `user` ON `user`.`id_user` = `anggota`.`id_user` UNION
SELECT pelatih.id_user, pelatih.nama,user.password,user.username FROM (`pelatih`)
LEFT JOIN `user` ON `user`.`id_user` = `pelatih`.`id_user` UNION
SELECT admin.id_user, admin.nama, user.password,user.username FROM (`admin`)
LEFT JOIN `user` ON `user`.`id_user` = `admin`.`id_user`) userdata WHERE `username` LIKE username
ORDER BY `userdata`.`id_user`
");
return $data;
}
Please tell me how to change the query to solve this.
Thank you.