How to exclude data which is already matched to the other table? MySQL CodeIgniter

user_13806771 0
Viewed 33

I need to create a report of not submitted grades and fetch all data of not submitted grades.

I have two tables, tbl_college_grades and tbl_fm_college_curriculuminfo

1st table - tbl_college_grades - fetch data which have grades

2nd table - tbl_fm_college_curriculuminfo - fetch data with all subjects info

I need to hide or exclude data in tbl_fm_college_curriculuminfo in which tbl_college_grades already have.

Is there any way to achieve this?

Here is my model and query, I have tried to use NOT EXISTS but here is the error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS(SELECT tbl_college_grades.subCode FROM tbl_college_grades WHERE tbl_co...'

Code:

function report(){
    $this->db->join('tbl_college_grades','tbl_college_grades.subCode = tbl_fm_college_curriculuminfo.subjectCode','left');
    $this->db->group_by('subject');
    $this->db->where('NOT EXISTS(SELECT tbl_college_grades.subCode FROM tbl_college_grades WHERE tbl_college_grades.subCode = tbl_fm_college_curriculuminfo.subjectCode)');
    $query = $this->db->get('tbl_fm_college_curriculuminfo');
    return $query->result_array();
}
codeigniter-3 codeigniter mysql
1 Answers

Use this

Preview in SQLFiddle

$this->db->group_by('tbl_fm_college_curriculuminfo.subject'); use proper table to subject. I assumed it was placed in tbl_fm_college_curriculuminfo

function report(){
    $this->db->select('tbl_fm_college_curriculuminfo.*');
    $this->db->from('tbl_fm_college_curriculuminfo');
    $this->db->join('tbl_college_grades', 'tbl_college_grades.subCode = tbl_fm_college_curriculuminfo.subjectCode', 'left');
    $this->db->where('NOT EXISTS (Select tbl_fm_college_curriculuminfo.subjectCode from tbl_college_grades WHERE tbl_college_grades.subCode = tbl_fm_college_curriculuminfo.subjectCode');
    $this->db->group_by('tbl_fm_college_curriculuminfo.subject');
    $query = $this->db->get();
}

group_by always comes at last (before data fetch ->get())

user_45956750
Related