Mysql where not exist in codeigniter with left join

Question

I have 2 tables

tablea
id_a | name_a
-------------
  1  | one
  2  | two
  3  | three
  4  | four
  5  | five
  6  | six

tableb
id_b | name_b
-------------
  1  | a
  2  | b

Ì want the result is

id_a | id_b | name_b
----------------------------
  1  |  1   |  a
  2  |  2   |  b
  3  | null | null
  4  | null | null
  5  | null | null
  6  | null | null

in that tables I want convert this mysql query

SELECT * FROM tablea
WHERE NOT EXISTS (SELECT * FROM tableb WHERE tableb.`id_b`=tablea.`id_a`)

into codeigniter query

I tried with this query but is not working

$this->db->join('tableb', 'tableb.id_ab=tablea.id_a','left'); 
$q = $this->db->get_where('tabela',array('tableb.id_b !='=> 4));
return $q->result_array();

I take the example of looking for an id (id_b) that is not 4, which of course is not in table b and the result in null

please help me. thanks a lot

Answer 1

You can alternatively use CodeIgniter db query.

$this->db->query('
     SELECT tableA.id_a,
            tableB.id_b,
            tableB.name_b
     FROM tableA
     LEFT JOIN tableB ON tableB.id_b = tableA.id_a
     WHERE tableB.id_b != "4" 
     OR tableB.id_b IS NULL)
');