...

View Full Version : Problem in WHERE Clause from SELECT Statement



newphpcoder
12-14-2011, 12:59 AM
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

Old Pedant
12-14-2011, 01:24 AM
Look here:

http://www.codingforums.com/showthread.php?p=818192#post818192

You can *NOT* use a WHERE condition on the right side table of a LEFT JOIN without converting the LEFT JOIN to an INNER JOIN, which typically means you don't get all the records you wanted.

Just move the condition from the WHERE to the ON.



$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'";



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum