Mysql query using UNION

Question

$sql = "SELECT `employee`.`employee_id`, `employee`.`employee_pre_code`, `employee`.`employee_code`, `employee`.`employee_name`, `employee`.`active`, `designation`.`designation_name`, `employeetype`.`employee_type_name`, `supervisor`.`supervisor_id`, `incharge`.`incharge_id`, `subsection`.`subsection_id`, `section`.`section_id`, `floor`.`floor_id`, `unit`.`unit_id`, `attendance_summery`.`present_days`,`attendance_summery`.`working_days` 
          FROM `employee` LEFT JOIN `designation` ON `employee`.`designation_id`=`designation`.`designation_id` 
          LEFT JOIN `employeetype` ON `employee`.`employee_type_id` = `employeetype`.`employee_type_id`
          LEFT JOIN `supervisor` ON `supervisor`.`supervisor_id` = `employee`.`supervisor_id` 
          LEFT JOIN `incharge` ON `incharge`.`incharge_id` = `supervisor`.`incharge_id` 
          LEFT JOIN `subsection` ON `subsection`.`subsection_id` = `incharge`.`subsection_id` 
          LEFT JOIN `section` ON `section`.`section_id` = `subsection`.`section_id` 
          LEFT JOIN `floor` ON `floor`.`floor_id` = `section`.`floor_id` 
          LEFT JOIN `unit` ON `unit`.`unit_id` = `floor`.`floor_id`    
          WHERE `employee`.`unit_id` = '1' AND `employee`.`active` = 1 
UNION
        SELECT `employee`.`employee_id`, `employee`.`employee_pre_code`, `employee`.`employee_code`, `employee`.`employee_name`, `employee`.`active`, `designation`.`designation_name`, `employeetype`.`employee_type_name`, `supervisor`.`supervisor_id`, `incharge`.`incharge_id`, `subsection`.`subsection_id`, `section`.`section_id`, `floor`.`floor_id`, `unit`.`unit_id`, `attendance_summery`.`present_days`,`attendance_summery`.`working_days` FROM `employee` 
          LEFT JOIN `attendance_summery` ON `attendance_summery`.`employee_id` = `employee`.`employee_id` 
          WHERE `attendance_summery`.`payment_period_id` = 36 
          ORDER BY `employee`.`employee_name` ASC";

$query = $this->db->query($sql);
return $query->result_array();

A Database Error Occurred

Error Number: 1054

Unknown column 'attendance_summery.present_days' in 'field list'

But the column 'attendance_summery.present_days' exists.

I failed to find the problem with the query.

Can any one help.

Its probably a typing mistake, double check the spellings of the table and field name — mynawaz
– mynawaz, Commented Sep 20, 2015 at 5:33

Nitin Tripathi · Accepted Answer · 2015-09-20 05:49:55Z

1

don't you think, attendance_summery table should not be a part of FROM clause of first query while it is part of select list? I cant see attendance_summery table in FROM CALUSE which is miss!!

edited Sep 20, 2015 at 5:49

answered Sep 20, 2015 at 5:41

Nitin Tripathi

1,2648 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Hafsul Maru Over a year ago

I need to join a field from attendance_summery to employee table data.

Nitin Tripathi Over a year ago

rephrase it above.. what i meant is.. i can't see the attendance_summery table in first query while it is part of select list of first query.. which means.. what you are missing it is to add attendance_summery in FROM List and it should resolve it.

Hafsul Maru Over a year ago

So what would be the solution. can you please rewrite.

Nitin Tripathi Over a year ago

Join employee with attendance_summery in first query as you did in 2nd query FROM employee LEFT JOIN attendance_summery ON attendance_summery.employee_id = employee.employee_id

Hafsul Maru Over a year ago

joined but another message come Unknown column 'designation.designation_name' in 'field list

|

Jerome Anthony · Accepted Answer · 2015-09-20 06:07:29Z

1

attendance_summery table is not selected in the first query of the union.

You will have to add ;

LEFT JOIN `attendance_summery` ON `attendance_summery`.`employee_id` = `employee`.`employee_id

to the first select of your sql. You need to test each field and add them.

edited Sep 20, 2015 at 6:07

answered Sep 20, 2015 at 5:31

Jerome Anthony

8,1012 gold badges44 silver badges32 bronze badges

5 Comments

Hafsul Maru Over a year ago

'present_days' field only exists in attendance_summery table.thanks.

Hafsul Maru Over a year ago

Can you please help rewriting.

Hafsul Maru Over a year ago

joined but another message come Unknown column 'designation.designation_name' in 'field list'

Jerome Anthony Over a year ago

Have to tested your individual queries?

Hafsul Maru Over a year ago

I have tested the query without union and the where clause of the second select, then it works. Can you give me suggession how can I add the where clause of the 2nd clause anyway without union joining the working days of employees where payment_period_id is 36

Hafsul Maru · Accepted Answer · 2015-09-20 08:34:11Z

My main issue was to use multiple conditions in JOINing the tables. At last added two conditions when joining attendance_summery table with AND keyword which joins the rows that only matches $paymentPeriodId variable. That fulfilled what I wanted. Again thanks for Jerome Anthony and Nitin Tripathi. My updated query is as:

$sql = "SELECT `employee`.`employee_id`, `employee`.`employee_pre_code`, `employee`.`employee_code`, `employee`.`employee_name`, `employee`.`active`, `designation`.`designation_name`, `employeetype`.`employee_type_name`, `supervisor`.`supervisor_id`, `incharge`.`incharge_id`, `subsection`.`subsection_id`, `section`.`section_id`, `floor`.`floor_id`, `unit`.`unit_id`, `attendance_summery`.`present_days`, `attendance_summery`.`working_days` "
      . "FROM `employee` "
      . "LEFT JOIN `designation` ON `employee`.`designation_id`=`designation`.`designation_id`"
      . "LEFT JOIN `employeetype` ON `employee`.`employee_type_id` = `employeetype`.`employee_type_id`"
      . "LEFT JOIN `supervisor` ON `supervisor`.`supervisor_id` = `employee`.`supervisor_id`"
      . "LEFT JOIN `incharge` ON `incharge`.`incharge_id` = `supervisor`.`incharge_id`"
      . "LEFT JOIN `subsection` ON `subsection`.`subsection_id` = `incharge`.`subsection_id`"
      . "LEFT JOIN `section` ON `section`.`section_id` = `subsection`.`section_id`"
      . "LEFT JOIN `floor` ON `floor`.`floor_id` = `section`.`floor_id`"
      . "LEFT JOIN `unit` ON `unit`.`unit_id` = `floor`.`floor_id`"
      . "LEFT JOIN `attendance_summery` ON (`attendance_summery`.`employee_id` = `employee`.`employee_id` AND `attendance_summery`.`payment_period_id` = $paymentPeriodId )"
      . "WHERE `employee`.`unit_id` = '1' AND `employee`.`active` = '1' "
      . "ORDER BY `employee`.`employee_name` ASC"; 

      $query = $this->db->query($sql);
      return $query->result_array();

Collectives™ on Stack Overflow

Mysql query using UNION

3 Answers 3

8 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related