#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    10

    Query producing unexpected results


    The query below produces the desired results in that it returns only the results for those employees with their status set to 'A'.

    PHP Code:
    SELECT employee.badge_no
             
    employee.emp_lname
             
    employee.emp_fname
             
    employee.dept_id
             
    employee.ss_number
             
    employee.status
             
    employee.termination_date
             
    employee.birth_date
             
    punch_data.punch_timestamp
             
    punch_data.source
             
    punch_data.in_out
             
    d.dept_name
             
    DATEDIFF(yystart_date, {fn NOW()}) AS YOS
          FROM employee
        LEFT OUTER
          JOIN 
    SELECT badge_no
                      
    MAX(punch_timestamp) AS latest_punch
                   FROM punch_data
                 GROUP
                     BY badge_no 
    ) AS latest
            ON latest
    .badge_no employee.badge_no
        LEFT OUTER
          JOIN punch_data
            ON punch_data
    .badge_no latest.badge_no
           
    AND punch_data.punch_timestamp latest.latest_punch
        INNER 
          JOIN
            department 
    AS d
            ON d
    .dept_id employee.dept_id
         WHERE employee
    .status 'A' 
    However, when I add the following to the WHERE clause (the $els_search is the search term being passed by PHP).

    PHP Code:
    AND employee.emp_fname LIKE '%$els_search%' 
      
    OR employee.emp_lname LIKE '%$els_search%' 
    It begins to return INACTIVE employees that are designated with have an " I " in their employee.status field.

    Thanks in advance for your assistance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,388
    Rep Power
    392
    Code:
     where employee.status = 'A'
       and (employee.emp_fname LIKE '%$els_search%' 
        or employee.emp_lname LIKE '%$els_search%' )
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    10
    Originally Posted by swampBoogie
    Code:
     where employee.status = 'A'
       and (employee.emp_fname LIKE '%$els_search%' 
        or employee.emp_lname LIKE '%$els_search%' )
    Many thanks!

    DOOOOOH! Just so I actually make sure I know whats going on... The parentheses set operator precedence?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,388
    Rep Power
    392
    Yes.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    10
    Originally Posted by swampBoogie
    Yes.
    /Bow Thank you

IMN logo majestic logo threadwatch logo seochat tools logo