CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-3
irwan dwiyanto
irwan dwiyanto

Reputation: 690

Show data that is not in other table with join codeigniter

I have 2 tables, I try to join to combine the 2 tables with codeigniter, but when I create query join there is data that does not appear because the data is not in the other table again, how can I solve this problem ??

tbl_fruit
_______________
| id |  fruit | 
|----|--------|
| 0  | manggo | 
| 1  | apple  | 
| 2  | banana | 
| 3  | orange | 
| 4  | avocado| 

tbl_proc
_______________
| id |  proc  | 
|----|--------|
| 0  | juice  | 
| 1  | blend  | 
| 2  | shake  | 

tbl_master
____________________________________
| id | id_fruit | id_proc | client  |
|----|----------|---------|---------|
|  0 |     2    |         |         | //How display on join
|  1 |     1    |    2    | nana    |
|  2 |     3    |    2    | george  |
|  3 |     4    |    0    | smith   |
|  4 |     0    |         | billy   | //How display on join
|  5 |     2    |    1    | Jack    |

tbl_result
----------------------------------------
| no | name fruit | name proc | client |
|----|------------|-----------|--------|
|  0 |  apple     | shake     | nana   |
|  1 |  orange    | shake     | george |
|  2 |  avocado   | juice     | smith  |
|  3 |  banana    | blend     | smith  |    

    $query = $this->db->select('*')
                      ->from('tb_result')
                      ->join('tb_fruit', 'tb_fruit.id = tb_result.id_fruit')
                      ->join('tb_proc', 'tb_proc.id = tb_result.id_proc')
                      ->get();
    return $query->result();

how to display data on tbl_master that does not have data in other tables but appears in the table in the join?

Upvotes: 0

Views: 2288

Answers (1)

ivanavitdev
ivanavitdev

Reputation: 2888

Use LEFT JOIN at the third parameter of your join function

$query = $this->db->select('*')
                      ->from('tbl_master')
                      ->join('tbl_fruit', 'tbl_fruit.id = tbl_master.id_fruit', 'left')
                      ->join('tbl_proc', 'tbl_proc.id = tbl_master.id_proc', 'left')
                      ->get();
    return $query->result();

It should result into this SQLFIDDLE

Upvotes: 1

Related Questions