Problem in WHERE Clause from SELECT Statement

[newphpbees]

Hi...

 

I have a SELECT Statement to get the Rate and Hours per employee.

 

$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours 
FROM $ADODB_DB.employment AS em 
INNER JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
LEFT JOIN $PAYROLL.regular_sum_hours AS r ON em.EMP_NO = r.EMP_NO
WHERE  em.EMP_ID = '$currentEmpID'";
$RsEarnings = $conn2->Execute($sql); 

  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
  
$Amount = $_POST["Amount"];

$Amount = round(($Hours/8)* $Rate, 2); 

 

and Now I revised it, because I  need to add OT_Hours in Hours where the STATUS = 'OffSet'.

 

here is the revised code:

$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours, o.OT_Hours, o.STATUS 
FROM $ADODB_DB.employment AS em 
INNER JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
LEFT JOIN $PAYROLL.regular_sum_hours AS r ON em.EMP_NO = r.EMP_NO
LEFT JOIN $PAYROLL.ot_data AS o ON r.EMP_NO = o.EMP_NO
WHERE  em.EMP_ID = '$currentEmpID' AND o.STATUS = 'OffSet'";
$RsEarnings = $conn2->Execute($sql); 

  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Hours'];
  $Offset = $RsEarnings->fields['OT_Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
  $Hours = ($Hours + $Offset);
  
$Amount = $_POST["Amount"];

$Amount = round(($Hours/8)* $Rate, 2); 

 

When I run this revised code I noticed that if the employee has no data in ot_data OT_Hours where STATUS = 'OffSet' the Rate and Hours will not displayed.

 

I want it even the employee has no data on ot_data the Rate and Hours will still display..

 

I'm still find the solution for that.

 

Any help is highly appreciated..

 

Thank you

[requinix]

Move the "OffSet" condition into the JOIN.

[newphpbees]

i already resolved it using this code:

 

$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours, o.OT_Hours, o.STATUS 
FROM $ADODB_DB.employment AS em 
INNER JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
LEFT JOIN $PAYROLL.regular_sum_hours AS r ON em.EMP_NO = r.EMP_NO
LEFT JOIN $PAYROLL.ot_data AS o ON ( r.EMP_NO = o.EMP_NO AND o.STATUS = 'OffSet' )
WHERE  em.EMP_ID = '$currentEmpID'";

 

Thank you