Codeigniter join table alias – Codeigniter Database Class is used to join tables in coddiegniter. $this->db->join(); is used to join tables in codeigniter. It is very simple to join tables using alias name. Here in this tutorial, we are going to explain how you can use table alias to join tables in CI.

Codeigniter join table alias

Here is simple example of join tables using alias name. We have taken alias name for users table as u, profile_image as pi, city as c, post as p and friends as f.

$this->db->select('u.id,u.name,pi.image,p.post,f.id as fid');
$this->db->from('users as u');
$this->db->join('profile_image as pi ', 'pi.user_id = u.id');
$this->db->join('city as c', 'c.user_id = u.id','left');
$this->db->join('post as p ', 'p.user_id = u.id','left');
$this->db->join('friends as f ', 'f.user_id = u.id','left');
$this->db->where('u.id', $id); 
$query = $this->db->get();

The above example will produce output something like this-
// Select u.id,u.name,pi.image,p.post,f.id as fid from users
join profile_image as pi on profile_image.user_id = users.id
left join city as c on c.user_id = u.id
left join post as p on p.user_id = u.id
left join friends as f on f.user_id = u.id
where u.id = $id

Codeigniter join Multiple tables

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->join('post', 'post.user_id = users.id','left');
$this->db->join('friends', 'friends.user_id = users.id','left');
$this->db->where('users.id', $id); 
$query = $this->db->get();

Will produce
// Select *from users
join profile_image on profile_image.user_id = users.id
left join city on city.user_id = users.id
left join post on post.user_id = users.id
left join friends on friends.user_id = users.id
where users.id = $id

Codeigniter join with multiple conditions

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', ' ( profile_image.user_id = users.id  AND  profile_image.is_active="1" )');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->where('users.id', $id); 
$query = $this->db->get();

Will produce
// Select *from users join profile_image
on ( profile_image.user_id = users.id AND profile_image.is_active=”1″ )
left join city on city.user_id = users.id where users.id = $id

Codeigniter join 3 tables It is very simple to join multiple tables in codeigniter using query builder class, $this->db->join(); is used to join two or more than two tables in codeigniter. Here in this article we are going to explain how you can join three tables in Codeigniter.

Codeigniter join 3 tables

Here is an example to join three tables – users, profile_image and city.

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->where('users.id', $id); 
$query = $this->db->get();

On the same way you can join two more than two table in codeigniter.
You can check more details about the query builder class in codeigniter here – Database Query Builder Class Or Main Documentation

The above example will generate the below query-

// Select *from users join profile_image on profile_image.user_id = users.id left join city on city.user_id = users.id where users.id = $id

Codeigniter left join query : $this->db->join(table_name, condition, ‘left’); is used for left join in codeigniter. We can left join as many tables using the same syntax. Here in this article, we are going to explain how you can use left join using Codeigniter Database standard.

Codeigniter left join query | Active Records example

Here in this we have joined two tables users and profile_image and we have performed join to get data collectively.

Example 1

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id','left');
$this->db->where('users.id', $id); 
$query = $this->db->get();

The above query will produce the following Query-

// Select *from users left join profile_image on profile_image.user_id = users.id where users.id = $id

Codeigniter left join query

Example 2

Here is another example of left join in Active Records.

$this->db->select('o.id, o.grand_total, o.sub_total, u.name');
$this->db->from('users u');
$this->db->join('sales_order o', 'o.user_id = u.id', 'left');
$this->db->where('o.user_id', 100);
$query = $this->db->get(); 

Codeigniter joins example

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$query = $this->db->get();

Will produce

// Select *from users join profile_image on profile_image.user_id = users.id

More Codeigniter joins example

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->where('users.id', $id); 
$query = $this->db->get();

Will produce the query

// Select *from users join profile_image on profile_image.user_id = users.id where users.id = $id