php - how to union all this query code in codeigniter -

"table 1"

$this->db->select('referral1.*, client.*, employee.* ');       $this->db->from('client');       $this->db->join('referral1', 'client.referral_id = referral1.referral1_id', 'inner' );       $this->db->join('assign_psychotherapist ', 'assign_psychotherapist.a_referral_id = client.referral_id', 'inner' );       $this->db->join('employee ', 'assign_psychotherapist.a_psychotherapist_id  = employee.empid', 'inner' );       $this->db->where("referral_status ='assigned' or referral_status ='accepted' ");       $this->db->order_by("referral_date", "desc"); 

"table2"

 $this->db->select('referral1.*, client.*, volunteer.* ');       $this->db->from('client');       $this->db->join('referral1', 'client.referral_id = referral1.referral1_id', 'inner' );       $this->db->join('assignvolunteer', 'assignvolunteer.vreferralid = client.referral_id', 'inner' );       $this->db->join('volunteer', 'assignvolunteer.vvolunteerid = volunteer.volid', 'inner' );       $this->db->where("referral_status ='assigned' or referral_status ='accepted' ");       $this->db->order_by("referral_date", "desc"); 

how can join 2 queries together?

you can this:

$sql = (" select *  ( select r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,e.employee_nickname client c inner join referral1 r on client.referral_id = referral.referral1_id inner join assign_psychotherapist ap on ap.a_referral_id = c.referral_id inner join employee e on ap.a_psychotherapist_id = e.empid referral_status ='assigned' or referral_status ='accepted' order referral_date desc ) union select *  ( select r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,v.volunteer_nickname client c inner join referral1 r on c.referral_id = r.referral1_id inner join assignvolunteer av on av.vreferralid = c.referral_id inner join volunteer v on v.vvolunteerid = v.volid referral_status ='assigned' or referral_status ='accepted' order referral_date desc )  "); $this->db->query($sql); 

since code igniter not support union in active record of version 2.2.1, can use query strings achieve desired result.

side note: when using union all sure queries being joined contains same amount , same positioning of columns achieve want..

you can use table alias further shorten query , makes more organized.