How to join 2 table and return value

Question

I have 2 tables

first table :

id | order_number | product_id
1  | 111111       | 121
2  | 222222       | 343
3  | 333333       | 344

second table

id | order_number | paid
3  | 111111       | 1
6  | 222222       | 1

Can i take all data in first table that is not owned by the second table ?

example

order_number 333333 is not owned by the second table.

how is the code for the above case using framework codeigniter 3 ?

I have tried below code but return same value at 2 table

$query = $this->db->select('*') 
         ->from('first_tbl') 
         ->join('second_tbl', 'second_tbl.order_number = first_tbl.order_number', 'right') 
         ->get();
var_dump($query->result_array());

sorry for bad english

You have to show us your code efforts, what you have tried so far. — Alive to die - Anant, Apr 24 '19 at 06:20
i try this code but return same value at 2 table $query = $this->db->select('*') ->from('first_tbl') ->join('second_tbl', 'second_tbl.order_number = first_tbl.order_number', 'right') ->get(); var_dump($query->result_array()); — Handi, Apr 24 '19 at 06:26

score 0 · Accepted Answer · answered Apr 24 '19 at 06:37

You can use this query

 SELECT t1.* FROM first_tbl AS t1
 LEFT JOIN second_tbl AS t2 ON t1.order_number = t2.order_number
 WHERE t2.ID IS NULl

And it will return only the 333333 record

See this DB fiddle for example

This is how it would look in CI:

$this->db->select("t1.*")
$this->db->from("first_tbl AS t1");
$this->db->join("second_tbl AS t2 ", "t1.order_number = t2.order_number", "left");
$this->db->where("t2.id IS NULL");

var_dum($this->db->get()->result());

score 0 · Answer 2 · answered Apr 24 '19 at 07:51

    $this->db->SELECT('table1.order_number,table1.product_id,table2.paid');
    $this->db->FROM('table1');
    $this->db->JOIN('table2', 'table2.order_number = table1.order_number', 'LEFT');
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return false;
    }

How to join 2 table and return value

2 Answers2