How to Join Three Tables in Codeigniter

How to JOIN three tables in Codeigniter

Use this code in model

public function funcname($id)
{
    $this->db->select('*');
    $this->db->from('Album a'); 
    $this->db->join('Category b', 'b.cat_id=a.cat_id', 'left');
    $this->db->join('Soundtrack c', 'c.album_id=a.album_id', 'left');
    $this->db->where('c.album_id',$id);
    $this->db->order_by('c.track_title','asc');         
    $query = $this->db->get(); 
    if($query->num_rows() != 0)
    {
        return $query->result_array();
    }
    else
    {
        return false;
    }
}

How to INNER JOIN 3 tables using CodeIgniter

it should be like that,

$this->db->select('*');    
$this->db->from('table1');
$this->db->join('table2', 'table1.id = table2.id');
$this->db->join('table3', 'table1.id = table3.id');
$query = $this->db->get();

as per CodeIgniters active record framework

How to join three tables using Codeigniter if there is no common fields?

select d.id,bm.bank_name
from Deposit_Details d
inner join Deposit_Master dm on dm.deposit_master_id = d.deposit_master_id
inner join Bank_Master bm on bm.bank_id = dm.bank_id

$this->db->select('Deposit_Details.id,Bank_Master.bank_name');
$this->db->from('Deposit_Details');
$this->db->join('Deposit_Master', 'Deposit_Master.deposit_master_id = Deposit_Details.deposit_master_id');
$this->db->join('Bank_Master', 'Bank_Master.bank_id = Deposit_Master.bank_id');
$query = $this->db->get();

join 3 tables with codeigniter

i got it by changing my code like this

public function attendance_report_by_empid($user_id = null, $sdate = null) {

$this->db->select('attendance.*', FALSE);
$this->db->select('employee.*', FALSE);
$this->db->select('salary.*', FALSE);

$this->db->from('attendance');

$this->db->join('employee', 'employee.user_id  = attendance.user_id', 'left');
$this->db->join('salary', 'salary.salary_template_id  = employee.salary_template_id', 'inner');
$this->db->where('attendance.user_id', $user_id);
$this->db->where('attendance.date_in', $sdate);
$this->db->where('attendance.date_out <=', $sdate);

$query_result = $this->db->get();
$result = $query_result->result();

return $query;

}

the problem was we have to declare the table before using that table for join.

join 3 tables in mysql codeigniter

You are referring to tbl_tickets_replies twice.
Try this:

function fetch_comments($ticket_id){
    $this->db->select('tbl_tickets_replies.comments, 
           tbl_users.username,tbl_roles.role_name');
    $this->db->where('tbl_tickets_replies.ticket_id',$ticket_id);
    $this->db->join('tbl_users','tbl_users.id = tbl_tickets_replies.user_id');
    $this->db->join('tbl_roles','tbl_roles.role_id=tbl_tickets_replies.role_id');
    return $this->db->get('tbl_tickets_replies');
}

Join three table codeigniter 4

What you currently have won't work because your Table_1 and Table_2 are effectively the same table.

Taking your Attempt and correcting it to use LEFT JOIN

public function example_1() {
    $this->join('Table_1', 'Table_1.unique_id = Table_3.author', 'LEFT');
    $this->join('Table_2', 'Table_2.unique_id = Table_3.author', 'LEFT');
    $this->select('Table_1.Name');
    $this->select('Table_2.Name');
    $this->select('Table_3.*');
    $this->orderBy('Table_3.id');
    $result = $this->findAll();

    echo $this->db->getLastQuery();

    return $result;
}

You would get...

SELECT `Table_1`.`Name`, `Table_2`.`Name`, `Table_3`.*
FROM `Table_3`
LEFT JOIN `Table_1` ON `Table_1`.`unique_id` = `Table_3`.`author`
LEFT JOIN `Table_2` ON `Table_2`.`unique_id` = `Table_3`.`author`
ORDER BY `Table_3`.`id`

array(3) {
  [0]=>
  array(4) {
    ["Name"]=>
    NULL
    ["id"]=>
    string(1) "1"
    ["author"]=>
    string(4) "1111"
    ["title"]=>
    string(6) "Book_1"
  }
  [1]=>
  array(4) {
    ["Name"]=>
    string(4) "Alex"
    ["id"]=>
    string(1) "2"
    ["author"]=>
    string(4) "5151"
    ["title"]=>
    string(6) "Book_2"
  }
  [2]=>
  array(4) {
    ["Name"]=>
    NULL
    ["id"]=>
    string(1) "3"
    ["author"]=>
    string(4) "1111"
    ["title"]=>
    string(6) "Book_3"
  }
}

Note that you have, two occurrences of name in your query. So which one will win? It appears that Table_2.name is only used and any reference to Table_1.name is NULL as it's not used.

You could give them different names using aliases but then you would have something like name_1 and name_2 so which one is it? This is due to the duplication in your Table_1 and Table_2 and you asking for both.

The Better way
So in this case you would need to perform an UNION on Table_1 and Table_2.

I don't think that there is a UNION command in the CI query builder.

Using mysql, it would be...

public function get_book_and_author() {
    $sql = "SELECT Table_3.id, T12.name as author, Table_3.title 
            FROM (
                SELECT Table_1.* FROM Table_1
            UNION
                SELECT Table_2.* FROM Table_2
                ) as T12
            LEFT JOIN Table_3 ON T12.unique_id = Table_3.author  
            WHERE Table_3.author IS NOT NULL
            ";
    $result = $this->db->query($sql);

    return $result->getResultArray();
}

So in this example, we have specified the 3 fields you require in the Select. Note the T12.name is renamed author. (See the output below)

Then an UNION has to be performed on Table_1 and Table_2 and the result is named (aliased) as T12 (shorthand for Table_1 and Table_2) as the result requires a new name.

Then a LEFT JOIN is performed against Table_3, which will give all combinations where there will be NULLS, so the WHERE statement filters those out using "IS NOT NULL" on Table_3.author.

I left out the ORDER BY as it's not really needed and you can add that back in if you wish to.

A var_dump() of the result gives...

array(3) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["author"]=>
    string(3) "Sam"
    ["title"]=>
    string(6) "Book_1"
  }
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["author"]=>
    string(4) "Alex"
    ["title"]=>
    string(6) "Book_2"
  }
  [2]=>
  array(3) {
    ["id"]=>
    string(1) "3"
    ["author"]=>
    string(3) "Sam"
    ["title"]=>
    string(6) "Book_3"
  }
}

So that will give you the id,author and title for each matching row as you have requested using your example Tables.

Codeigniter: Join between three tables

$query = $this->db->select('Meetings.*, Persons.name, Persons.surname, Companions.*')
        ->from('Meetings')
        ->join('Companions', 'Meetings.id = Companions.meeting_id', 'LEFT')
        ->join('Persons', 'Meetings.docent_id = Persons.id', 'INNER')
        ->where('Persons.delete_date is null')

be sure you have fully qualified column name for the where (could be you have the same column name in two table joined)

codeigniter join 2 table data

It doesn't matter what table is first... Simply:

<?php

$this->db->select('t1.name, t2.something, t3.another')
     ->from('table1 as t1')
     ->where('t1.id', $id)
     ->join('table2 as t2', 't1.id = t2.id', 'LEFT')
     ->join('table3 as t3', 't1.id = t3.id', 'LEFT')
     ->get();
?>

---

Related Topics