Codeigniter 3 - Query Builder 'join' Method '!=' operator is not giving expected output

Question

Not a Duplicate Question!!!

I am using CodeIgniter 3 - Query Builder Class with MySQLi.

Tables in DB:

'category_level_1' Table:

'category_level_2' Table:

Query in model.php:

$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();

Output :

Inner-Join not working.

Expected Output :

Only need to output records in 'category_level_1' Table which are not related with 'category_level_2' Table.

Issue:

As showed above, output values are not as expected according to '!=' operator is not working with 'inner' join.

Just had a quick read of this, and although I'm not an SQL expert, joining tables using 'not equals' (!=) seems weird. I would use 'equal' (=) and put the condition `category_level_2.cat_lvl1_id != category_level_1.id` in a `where` clause. — KolaB, May 13 '18 at 18:00

score 4 · Answer 1 · answered May 13 '18 at 22:45

4

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
        )
)

answered May 13 '18 at 22:45

Pradeep

9,667
13
27
34

Thanks. I'll try this way and let you know :) – Pasindu Jayanath May 14 '18 at 04:00
It's working also. Thanks for your effort. That way is nice for who are not aware of CodeIgniter Query Builder Classs. – Pasindu Jayanath May 14 '18 at 06:25

KolaB · Accepted Answer · 2018-05-13T18:42:10.660

2

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();

edited May 13 '18 at 18:42

answered May 13 '18 at 18:35

KolaB

501
3
11

I'll try that and let you know :) – Pasindu Jayanath May 13 '18 at 18:52

score 0 · Answer 3 · answered May 13 '18 at 18:23

0

$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();

answered May 13 '18 at 18:23

sumit saini

32
3

I'll try that and let you know :) – Pasindu Jayanath May 13 '18 at 18:52

Codeigniter 3 - Query Builder 'join' Method '!=' operator is not giving expected output

Tables in DB:

Query in model.php:

Output :

Expected Output :

Issue:

3 Answers3