CODEWITHSUNDEEP
mysqlselect
Hafsul Maru
Hafsul Maru

Reputation: 393

Why does my SQL query fetching more rows than are in the table?

There are 35 rows in salary_sheet table but this query fetches 30660 rows, dont know why?

 function queryCheck() {
  $sql = "select  salary_sheet.payment_period_id,salary_sheet.employee_id,salary_sheet.supervisorwise_serial,"
."salary_sheet.employee_type_id,salary_sheet.sl,salary_sheet.cl,salary_sheet.el,salary_sheet.basic,salary_sheet.hr,"
."salary_sheet.medical,salary_sheet.other,salary_sheet.total,salary_sheet.min_wages,"
."salary_sheet.prod_wages,salary_sheet.attn_bonus,salary_sheet.prod_bonus,"
."salary_sheet.ot_hours,salary_sheet.ot_rate,salary_sheet.ot_amount,salary_sheet.stamp_deduct,"
."salary_sheet.absent_deduction,salary_sheet.net_payable,"

."employee.employee_code,employee.employee_name,employee.designation_id,employee.employee_grade,"

."attendance_summery.working_days "
."from salary_sheet "
."left join employee on salary_sheet.employee_id = employee.employee_id  "
."left join attendance_summery on salary_sheet.payment_period_id = attendance_summery.payment_period_id    "
."where salary_sheet.employee_type_id = 9 and salary_sheet.payment_period_id = 41  "             
."order by salary_sheet.supervisorwise_serial desc";
$query = $this->db->query($sql);
$rows = $query->num_rows();
return $rows;

 }

Somebody's helps is appreciated.

Upvotes: 0

Views: 55

Answers (1)

Drew
Drew

Reputation: 24960

This is your query.

select  salary_sheet.payment_period_id,
salary_sheet.employee_id,
salary_sheet.supervisorwise_serial,
salary_sheet.employee_type_id,salary_sheet.sl,salary_sheet.cl,salary_sheet.el,salary_sheet.basic,salary_sheet.hr,
salary_sheet.medical,salary_sheet.other,salary_sheet.total,salary_sheet.min_wages,
salary_sheet.prod_wages,salary_sheet.attn_bonus,salary_sheet.prod_bonus,
salary_sheet.ot_hours,salary_sheet.ot_rate,salary_sheet.ot_amount,salary_sheet.stamp_deduct,
salary_sheet.absent_deduction,salary_sheet.net_payable,
employee.employee_code,employee.employee_name,employee.designation_id,employee.employee_grade,
attendance_summery.working_days
from salary_sheet
left join employee 
on salary_sheet.employee_id = employee.employee_id
left join attendance_summery 
on salary_sheet.payment_period_id = attendance_summery.payment_period_id
where salary_sheet.employee_type_id = 9 and salary_sheet.payment_period_id = 41             
order by salary_sheet.supervisorwise_serial desc

The first Left Join takes on the left the sheet rows that are of employee_type_id 9 and payment_period_id 41. All of those sheets are retained. And on the right side of the left join, all the rows come whether or not they exist for those employees (that is what a left join does). So they come if there, or null values if not there.

The result of that first step is the table on a left join, doing the same to table attendance_summary.

So yes, left joins have a duplicative effect to say the least, when you inadvertently make a mistake.

Upvotes: 2

Related Questions