Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Problem in WHERE Clause from SELECT Statement

    Hi...

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

    Code:
    $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:
    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Look here:

    http://www.codingforums.com/showthre...192#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.

    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'";
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-14-2011)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •