Delete with Join or Where_in not working

Hello, 

I would like to know the best way to delete with Query builder and join (not working).

This select with join working good with Query Builder and I would like to use the same way to delete but is not working. How I can do that ?

PHP Code:

//WORKS FINE    
public function get_services($profile_id, $location_id)
    {
        $this->db->select('s.*,l.location_id,l.profile_id');
        $this->db->from('service s');
        $this->db->join('location l', 's.location_id = l.location_id', 'left');
        $this->db->where('s.location_id', $location_id);
        $this->db->where('l.profile_id', $profile_id);

        $this->db->order_by("s.service_title", "ASC");

        $query = $this->db->get();
        return $query->result();
    } 


PHP Code:

// NOT WORKING    
public function delete_service($location_id,$service_id,$profile_id)
    {
        $this->db->from('service s');
        $this->db->join('location l', 's.location_id = l.location_id', 'left');
        $this->db->where('s.service_id', $service_id);
        $this->db->where('s.location_id', $location_id);
        $this->db->where('l.profile_id', $profile_id);

        return $this->db->delete('service s');
    } 


PHP Code:

// NOT WORKING    
public function delete_service($location_id,$service_id,$profile_id)
    {
        $this->db->select('s.*,l.location_id,l.profile_id');
        $this->db->from('service s');
        $this->db->join('location l', 's.location_id = l.location_id', 'left');
        $this->db->where('l.profile_id', $profile_id);
        $this->db->where('s.location_id', $location_id);
        $this->db->where('s.service_id', $service_id);

        $where_clause = $this->db->get_compiled_select();
        
        $this->db->where_in('s.location_id', $where_clause);

        return $this->db->delete('service s');
    }