I'm trying to send this query:
SELECT * FROM employees
INNER JOIN authorization ON authorization.Employee_ID=employees.Employee_ID
WHERE authorization.role='Team Leader'
AND authorization.Employee_ID NOT IN(SELECT Employee_ID FROM team_leaders)
This query is used to select employees who have a Team Leader position(role) and not assigned to a team.
I'm having trouble doing this query in CodeIgniter's active records
My incorrect model code:
function teamleaders()
{
$this->db->select('Employee_ID');
$this->db->from('team_leaders');
$query=$this->db->get();
$teamleaderid=$query->result_array();
//get teamleaders id who are assigned in a team
$this->db->select('employees.Employee_ID,employees.First_Name,employees.Last_Name');
$this->db->from('employees');
$this->db->join('authorization','authorization.Employee_ID=employees.Employee_ID');
$this->db->where('authorization.role','Team Leader');
$this->db->where_not_in('authorization.Employee_ID',$teamleadersid);
$query=$this->db->get();
return $query->result_array();
}
Yes you can do it Try,
$this->db->select('employees.*');
$this->db->from('employees');
$this->db->join('authorization','authorization.Employee_ID=employees.Employee_ID');
$this->db->where('authorization.role','Team Leader');
$this->db->where('authorization.Employee_ID NOT IN (SELECT Employee_ID FROM team_leaders)');
$query=$this->db->get();