How to Write SubQuery in CodeIgniter Active Record?

Today, We want to share with you subquery in codeigniter.In this post we will show you get value from subquery in CodeIgniter, hear for subquery in codeigniter join we will give you demo and example for implement.In this post, we will learn about Sql Subquery Multiple Tables Example with an example.

codeigniter join select field

SubQuery in CodeIgniter

$this->db->select('MemberID');
$this->db->from('Upcommings');
$sub_query = $this->db->get_compiled_select();

$this->db->select('MemberID, MemberName, CartName');
$this->db->from('Members');
$this->db->where("MemberID IN ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT MemberID, MemberName, CartName
// FROM Members
// WHERE MemberID IN (SELECT MemberID FROM Upcommings);

Next is second example of query igniter subquery which picks up all member records with salary greater than that of average salary.

Read Also:  PHP Inline Editing Table using jQuery Ajax

$this->db->select_avg('Age');
$this->db->from('Members');
$sub_query = $this->db->get_compiled_select();

$this->db->select('*');
$this->db->from('Members');
$this->db->where("Age > ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT * 
// FROM Members 
// WHERE Age > (SELECT AVG(Age) FROM Members);

query with active record methods

SELECT * FROM members WHERE id IN(SELECT id FROM members_backup); 

Sub Query

// Sub Query
$this->db->select('id')->from('members_backup');
$subQuery =  $this->db->get_compiled_select();

// Main Query
$this->db->select('*')
         ->from('members')
         ->where("id IN ($subQuery)", NULL, FALSE)
         ->get()
         ->result();

Subquery In Codeigniter Active Records

$this->db->select('*')->from('Members');
$this->db->where('id NOT IN (SELECT id FROM users_master where is_active = "1")', NULL, FALSE);

I hope you get an idea about subquery in codeigniter.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.