Issue in NULL values

[newphpcoder]

I got an issue in null values and I need to query between to two tables to get the data with non null values.

 

here is the 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:

 

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 :

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

[fenway]

I feel like you've asked this question before.

[newphpcoder]

Yes...I already ask this before and sad to say right now i did not fix this problem..

 

Thank you

[fenway]

Then let me know which thread this applied to, and I'll merge them.