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 SELECT Statement

    i encountered problem in my select statement.

    this is my query:
    Code:
    SELECT a.EMP_ID, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME, a.LOG_IN, a.LOG_OUT
     FROM hrdb.attendance AS a LEFT JOIN hris.employment em ON a.EMP_ID = em.EMP_NO 
     WHERE DATE(LOG_IN) BETWEEN '2011-12-08' AND '2011-12-23' 
     AND em.STATUS = 'Casual' OR LOG_IN IS NULL
    the output of this query is all data from attendance table was displayed eventhough their STATUS is not Casual.

    Thank you so much...

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,618
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    First of all, you no longer have a LEFT JOIN. Here is why:

    http://www.codingforums.com/showthre...192#post818192

    Not that it matters, because from you comments it is obvious that you *REALLY* want an INNER JOIN! Why did you use LEFT JOIN if you don't understand what it does?

    Secondly, you need to go back to elementary school arithmetic.

    What is the value of
    Code:
        3 + 4 * 5
    ??

    If you said 35, go back to school.

    Muliplication has higher PRECEDENCE than addition.

    The answer is 23.

    TO get 35 you would do
    Code:
        ( 3 + 4 ) * 5
    Same with AND and OR.

    So:
    Code:
    SELECT a.EMP_ID, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME, a.LOG_IN, a.LOG_OUT
     FROM hrdb.attendance AS a INNER JOIN hris.employment em ON a.EMP_ID = em.EMP_NO 
     WHERE DATE(LOG_IN) BETWEEN '2011-12-08' AND '2011-12-23' 
     AND ( em.STATUS = 'Casual' OR LOG_IN IS NULL )
    Note the added parentheses there.
    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.


  •  

    Posting Permissions

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