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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Question on how to use OR in a Select statement with where clause

    Hi...

    I just want to know how can I use OR in my select statement to check if the DATE is equal in NRS table or Reg Att table.

    Here is my real code without or to check if the DATE is in NRS not in regatt.

    here is my code now:
    in this code it works because the data is present in reg_att table
    Code:
    SELECT em.EMP_NO, p.EMP_ID, CONCAT(LNAME, ', ', FNAME, ' ', MI, '.') AS FULLNAME, DATE(a.LOGOUT) AS DATE_DTR, LOGIN AS min_dtr, LOGOUT AS max_dtr FROM hris.personal p, payroll.reg_att a, hris.employment em WHERE DATE(a.LOGOUT) = '2011-12-19' AND p.EMP_ID = '000252' and em.EMP_ID = '000252' AND em.EMP_NO = a.EMP_NO
    now that is possible that data is not present in reg_att but possible present in nrs table.
    and I tried this:
    Code:
    SELECT em.EMP_NO, p.EMP_ID, CONCAT(LNAME, ', ', FNAME, ' ', MI, '.') AS FULLNAME, DATE(a.LOGOUT) OR DATE(n.TIME_OUT) AS DATE_DTR, a.LOGIN OR n.TIME_IN AS min_dtr, LOGOUT OR TIME_OUT AS max_dtr FROM payroll.nrs n,hris.personal p, payroll.reg_att a, hris.employment em WHERE DATE(a.LOGOUT) OR DATE(n.TIME_OUT) = '2011-12-19' AND p.EMP_ID = '000252' and em.EMP_ID = '000252' AND em.EMP_NO = a.EMP_NO OR em.EMP_NO = n.EMP_NO;
    but it did not work.

    Thank you

  • #2
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,474
    Thanks
    63
    Thanked 537 Times in 524 Posts
    Wrong format.

    It should be:

    where something='else' OR anything='something'

    What I'm saying is you can't have
    where something OR anything='something'

    You must do each comparison seperately and then use OR.
    I can't really think of anything to write here now...

  • #3
    Regular Coder
    Join Date
    Jan 2012
    Posts
    134
    Thanks
    0
    Thanked 32 Times in 32 Posts
    You only use the "or" keyword inside of the where clause. Something like this:

    Code:
    select a, b or c from table where b=1 or c=2
    is not valid. Instead, use this:

    Code:
    select a, b, c from table where b=1 or c=2
    Also make sure to group your conditions accordingly.

    Code:
    where a=1 or b=2 and c=3
    is different than

    Code:
    where (a=1 or b=2) and c=3

  • Users who have thanked KuriosJon for this post:

    newphpcoder (02-16-2012)

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I revise my code suggested by other programmer.

    First Scenario: // the min_dtr has the 0000-00-00 00:00:00
    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    09900215-000089----Dela Cruz, Juan A.-2011-12-20--0000-00-00 00:00:00--2011-12-20 13:38:00
    09900215-000089----Dela Cruz, Juan A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 13:38:00

    and it works using this code:

    Code:
    
    SELECT em.EMP_NO
         , p.EMP_ID
         , CONCAT(LNAME , ', ' , FNAME , ' ' , MI , '.') AS FULLNAME
    
         , CASE DATE(a.LOGOUT) WHEN '0000-00-00'          THEN DATE(n.TIME_OUT) ELSE DATE(a.LOGOUT) END AS DATE_DTR
         , CASE a.LOGIN        WHEN '0000-00-00 00:00:00' THEN n.TIME_IN        ELSE a.LOGIN        END AS min_dtr
         , CASE a.LOGOUT       WHEN '0000-00-00 00:00:00' THEN n.TIME_OUT       ELSE a.LOGOUT       END AS max_dtr
    /* OR
         , COALESCE( NULLIF(DATE(a.LOGOUT) , '0000-00-00'         ) , DATE(n.TIME_OUT) ) AS DATE_DTR
         , COALESCE( NULLIF(a.LOGIN        , '0000-00-00 00:00:00') , n.TIME_IN        ) AS min_dtr
         , COALESCE( NULLIF(a.LOGOUT       , '0000-00-00 00:00:00') , n.TIME_OUT       ) AS max_dtr
    */
     FROM  hris.employment  em
     INNER JOIN
           hris.personal    p
      ON   p.EMP_ID = em.EMP_ID
     LEFT  OUTER JOIN
           payroll.reg_att  a
      ON   a.EMP_NO         = em.EMP_NO
       AND DATE(a.LOGOUT  ) = '2011-12-20'
     LEFT  OUTER JOIN
           payroll.nrs      n
      ON   n.EMP_NO         = em.EMP_NO
       AND DATE(n.TIME_OUT) = '2011-12-20'
     WHERE
           em.EMP_ID = '000089'
    ;
    the result of this code is :
    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------

    09900215-000089----Dela Cruz, Juan A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 13:38:00

    and it is correct

    and this is the second scenario: // the max_dtr has the 0000-00-00 00:00:00

    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    00900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--0000-00-00 00:00:00
    00900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 15:38:00
    I used this code :
    Code:
    SELECT em.EMP_NO
         , p.EMP_ID
         , CONCAT(LNAME , ', ' , FNAME , ' ' , MI , '.') AS FULLNAME
    
         , CASE DATE(a.LOGOUT) WHEN '0000-00-00'          THEN DATE(n.TIME_OUT) ELSE DATE(a.LOGOUT) END AS DATE_DTR
         , CASE a.LOGIN        WHEN '0000-00-00 00:00:00' THEN n.TIME_IN        ELSE a.LOGIN        END AS min_dtr
         , CASE a.LOGOUT       WHEN '0000-00-00 00:00:00' THEN n.TIME_OUT       ELSE a.LOGOUT       END AS max_dtr
    /* OR
         , COALESCE( NULLIF(DATE(a.LOGOUT) , '0000-00-00'         ) , DATE(n.TIME_OUT) ) AS DATE_DTR
         , COALESCE( NULLIF(a.LOGIN        , '0000-00-00 00:00:00') , n.TIME_IN        ) AS min_dtr
         , COALESCE( NULLIF(a.LOGOUT       , '0000-00-00 00:00:00') , n.TIME_OUT       ) AS max_dtr
    */
     FROM  hris.employment  em
     INNER JOIN
           hris.personal    p
      ON   p.EMP_ID = em.EMP_ID
     LEFT  OUTER JOIN
           payroll.reg_att  a
      ON   a.EMP_NO         = em.EMP_NO
       AND DATE(a.LOGOUT  ) = '2011-12-20'
     LEFT  OUTER JOIN
           payroll.nrs      n
      ON   n.EMP_NO         = em.EMP_NO
       AND DATE(n.TIME_OUT) = '2011-12-20'
     WHERE
           em.EMP_ID = '000252'
    ;
    and the output is:

    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    00900392-000252----Dela Cruz, John A.-(NULL)------(NULL)---------------(NULL)

    I want ouput is from nrs data because it is completed :

    EMP_NO---EMP_ID----FULLNAME-----------DATE_DTR----min_dtr--------------max_dtr------------
    00900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 15:38:00

    Thank you so much


  •  

    Posting Permissions

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