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 ...