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

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9

    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,383
    Rep Power
    391
    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
    9
    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,383
    Rep Power
    391
    Yes.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

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

IMN logo majestic logo threadwatch logo seochat tools logo