Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Question on how to use OR in a Select statement with where c

    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. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at using da manuel

    specifically
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    I tried UNION

    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-20' AND p.EMP_ID = '000089' and em.EMP_ID = '000089' AND em.EMP_NO = a.EMP_NO
    UNION
    SELECT em.EMP_NO, p.EMP_ID, CONCAT(LNAME, ', ',  FNAME, ' ', MI, '.') AS FULLNAME, DATE(n.TIME_OUT) AS DATE_DTR, n.TIME_IN AS min_dtr, n.TIME_OUT AS max_dtr FROM hris.personal p, PAYROLL.nrs n, hris.employment em WHERE DATE(n.TIME_OUT) = '2011-12-20' AND p.EMP_ID = '000089' and em.EMP_ID = '000089' AND em.EMP_NO = n.EMP_NO;

    in this code the output is two, it's true but I want to get only one.

    I have this data:

    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

    I want to get only is the complete data which is the second one.

    Thank you

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Example 1:
    Not tested.
    Code:
    SELECT em.EMP_NO
         , p.EMP_ID
         , CONCAT(LNAME , ', ' , FNAME , ' ' , MI , '.') AS FULLNAME
         , COALESCE( DATE(a.LOGOUT) , DATE(n.TIME_OUT) ) AS DATE_DTR
         , COALESCE( a.LOGIN        , n.TIME_IN        ) AS min_dtr
         , COALESCE( a.LOGOUT       , 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-19'
     LEFT  OUTER JOIN
           payroll.nrs      n
      ON   n.EMP_NO         = em.EMP_NO
       AND DATE(n.TIME_OUT) = '2011-12-19'
     WHERE
           em.EMP_ID = '000252'
    ;

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    I try your suggested code and the output is he gets only the data from reg_att table:

    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

    but as you can see the min_dtr is 0000-00-00 00:00:00, but he apply NRS to complete his attendance, so that in NRS table his data is completed, so I want the data in NRS will be get.

    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

    Thank you

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try to replace COALESCE functions with other functions/expressions.

    Example 2:
    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
    ...

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by tonkuma View Post
    Try to replace COALESCE functions with other functions/expressions.

    Example 2:
    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
    ...
    Thanks it works..is it work when the LOGOUT is 0000-00-00 00:00:00
    Thank you

  8. #8
    Join Date
    Dec 2010
    Posts
    136
    I used your suggested code in two different scenario.

    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
  •