Codeigniter 3 - Query Builder 'join' Method '!=' operator is not giving expected output
Hope this will help you :
$sql = "SELECT id, category FROM category_level_1 WHERE id NOT IN (SELECT DISTINCT cat_lvl1_id FROM category_level_2)";$query = $this->db->query($sql);print_r($query->result());
Output :
Array( [0] => stdClass Object ( [id] => 93 [category] => dummy ))
I suggest you try using a left
orright
join
and a where
clause. Give the following a go:
$query = $this->db ->select('category_level_1.id, category_level_1.category') ->from('category_level_1') ->join('category_level_2', 'category_level_2.cat_lvl1_id = category_level_1.id', 'left') ->where('category_level_2.cat_lvl1_id IS NULL') ->group_by('category_level_1.id') ->get();
$query = $this->db ->select('category_level_1.id, category_level_1.category') ->from('category_level_1') ->join('category_level_2', 'category_level_2.cat_lvl1_id <> category_level_1.id', 'inner') ->group_by('category_level_1.id') ->get();