return result from table that not existed on another table in codeignator-CodePudding

I've constructed two tables: one to list the services and another to link the service to each department.

Table 1 contains the columns [s id,s name]. [s_id,s_name].

-- The Services table, records all services for all departments. [1,Install Windows 10],[2,print Payslip].

Table 2 : services_assignments consist of columns [ss_id,ss_s_id_ss_d_id].

-- services_assignments matching the services with departments.

I need to return the service that "NOT" matched for the department example.

i tried to use JOIN with Where conditions in selection but not result as the following code.

function get_services_for_assign(){ // for assigmnets
        $this->db->select('*');
        $this->db->from('services');
        if($this->uri->segment('2')) {
            $this->db->join('services_assignments','services.sr_id = services_assignments.ss_s_id','left');
            $this->db->where_not_in('services_assignments.ss_d_id',$this->uri->segment('2'));
        }
        //$this->db->where('sr_display','1');
        $data=$this->db->get();
        return $data->result();
    }

On the other hand, I attempted to write it manually as seen below.

function get_services_for_assign(){ // for assigmnets

        $dep=$this->uri->segment('2');
        $query = "SELECT sr.sr_id FROM services AS sr WHERE sr.sr_id = (SELECT sa.ss_s_id FROM services_assignments AS sa WHERE sa.ss_d_id = 1)";
        $this->db->query($query);
        $data=$this->db->get();
        return $data->result();
    }

and I encountered the following error; I discovered numerous results that were similar to my problem, but I couldn't solve it.

A Database Error Occurred

Error Number: 1096

No tables used

SELECT *

Filename: C:/xampp/htdocs/townteam/system/database/DB_driver.php

Line Number: 691

I need your help to return the results services that were not used by the other department.

CodePudding user response：

You have not stated which version of CodeIgniter you are using or what the expected behaviour is when no department id is passed.

This should work in both CI3 and CI4. If using CI4, you should probably use prepared statements. I could not find documentation on passing parameters into a multi-condition join with CI so I have written the query out in full.

$sql = <<<'SQL'
SELECT s.*
FROM services s
LEFT JOIN services_assignments sa
    ON s.sr_id = sa.ss_s_id
    AND sa.ss_d_id = ?
WHERE sa.ss_s_id IS NULL
AND s.sr_display = 1
SQL;

$this->db->query($sql, [$this->uri->segment('2')]);