SELECT rows WHERE value not found in another table using CodeIgniter's query builder
20:17 10 May 2018

I want to produce a dropdown of choices from table1. But if the data is existing in table2 I will not include it in the choices. I'm aware about the outer excluding join but I can't implement it in CodeIgniter. Do I have other choice rather than implementing the Outer Excluding Join in CodeIgniter? I'm currently using CodeIgniter3. This is my code so far. I'm trying to get the items in the inventory table which is not in the inven_trans table.

$this->db
    ->select('brand')
    ->from('inventory')
    ->join('inven_trans', 'inventory.brand = inven_trans.brand', 'inner')
    ->where("NOT EXISTS( SELECT brand FROM inven_trans WHERE inventory.brand = inven_trans.brand)");

inventory

  • invenID
  • brand
  • quantity
  • product_num

inven_trans

  • intransID
  • invenID
  • brand
  • datereleased
  • datereturned

EDIT The code is working now although after adding another where clause it seems like it is not functioning (the where clause). This question is now closed but if someone is willing to help me I'll gladly accept it. The code is this

$data = $this->db
    ->select("*")
    ->from("inventory")
    ->where("brand NOT IN (SELECT brand FROM ha_inventory_trans WHERE date_returned != '00 00 0000')", NULL, FALSE)
    ->get()
    ->result_array();

I want to output the list of brand that has not been returned( date_returned is NULL or 0000 00 00)

php codeigniter codeigniter-3 query-builder notin