CodeIgniter active record SELECT query with JOIN and WHERE IN subquery

Question

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();
}

Answer 1

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();