Rjgapz Rjgapz - 5 months ago 42
SQL Question

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 I join the two queries together?

Answer

you can do something like this:

$sql = ("
SELECT * 
FROM (
select r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,e.employee_nickname
from 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
where referral_status ='Assigned' OR referral_status ='Accepted'
order by referral_date desc
)
union all
SELECT * FROM 
(
select r.referral_date,c.lastname,c.middlename,c.firstname,c.gender,r.presenting_problem,v.volunteer_nickname
from 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
where referral_status ='Assigned' OR referral_status ='Accepted'
order by referral_date desc
)
 ");
$this->db->query($sql);

Since Code Igniter does not support UNION in active record as of version 2.2.1, you can use query strings to achieve the desired result.

Side note: When using UNION ALL be sure that the queries being joined contains the same amount and same positioning of columns to achieve what you want..

You can also use table alias to further shorten your query and makes it more organized.

Comments