how to union all this query code in codeigniter

Question

"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?

Arzgethalm · Accepted Answer · 2015-04-01T17:19:31.763

0

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.

edited Apr 01 '15 at 17:19

answered Apr 01 '15 at 15:29

Arzgethalm

516
5
14

there's an error "Incorrect usage of UNION and ORDER BY" – Rjgapz Apr 01 '15 at 15:42
ok sorry for that. please check the updated answer, I have used * because I dont know exactly what are the columns that you will use. – Arzgethalm Apr 01 '15 at 15:46
thank you for the effort helping me but still i have an error "duplicate column name" – Rjgapz Apr 01 '15 at 16:41
you're welcome, :) can you enumerate all columns for table client, referrall,volunteer and employee?? – Arzgethalm Apr 01 '15 at 16:43
volunteer: volid volunteer_lastname volunteer_firstname volunteer_middlename volunteer_nickname birthday gender contact supervisor_id address status language acct_id – Rjgapz Apr 01 '15 at 16:58
referral1: referral1_id referral_by referral_by_type referral_date visitor relationship contact calledvisited presenting_problem doctor_remarks professional_finding referral_status referral_remarks intake_interview – Rjgapz Apr 01 '15 at 16:59
client: client_id referral_id lastname middlename firstname nickname age gender home_address active_language passive_language school grade class_hours start_date end_date client_status – Rjgapz Apr 01 '15 at 16:59
employee: empid acct_id lname fname mname cnumber position status birthdate gender address language employee_nickname – Rjgapz Apr 01 '15 at 16:59
ok that would be too many. what are the columns that you will need on your query anyway? you should take note that duplicate column names are now allowed. – Arzgethalm Apr 01 '15 at 17:08
referral1.referral_date, client.lastname client.middlename client.firstname, client.gender, referral1.presenting_problem, employee.employee_nickname or volunteer.volunteer_nickname only this 6 columns – Rjgapz Apr 01 '15 at 17:16
ok I have updated my answer, note: I only added the columns of your choice. – Arzgethalm Apr 01 '15 at 17:21
sorry for the interruption again i'm having trouble with this query. How can get the value using foreach method model: function getAllReferrals(){ $sql = ("query") $this->db->query($sql); } controller: $data['referrals'] = $this->all_referral_model->getAllReferrals(); $this->load->view('admin_views/all_referrals_view', $data); view: im having this error: Message: Invalid argument supplied for foreach() – Rjgapz Apr 01 '15 at 23:30

score 0 · Answer 2 · edited Jul 19 '16 at 11:10

SELECT * FROM
(
    SELECT DISTINCT assessment_topic.id AS NIRUPAM, assessment_topic.name, assessment_update.* FROM
    assessment_topic
    LEFT JOIN assessment_update ON assessment_topic.id = assessment_update.assess_id
    UNION
    SELECT assessment_topic.id, assessment_topic.name, '', 9, '', '', ''
      FROM assessment_topic
) A
WHERE A.school_id = 9
GROUP BY A.name
LIMIT 8

// 9 dynamic id pass and 8 dynamic limit pass

score -1 · Answer 3 · edited May 23 '17 at 10:28

-1

A much better answer can be found here - write union query in codeigniter style in short use get_compiled_select() and create your own query.

edited May 23 '17 at 10:28

Community

1
1

answered Jun 30 '16 at 15:03

Ukuser32

2,147
2
22
32

how to union all this query code in codeigniter

3 Answers3

Related