Problem in joining three tables

[newphpbees]

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

[jassmee]

you are used the join in tables... recheck the fields name and again check the table name ...