...

View Full Version : Question on how to use OR in a Select statement with where clause



newphpcoder
02-15-2012, 09:31 AM
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


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:


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

tangoforce
02-15-2012, 11:37 AM
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.

KuriosJon
02-15-2012, 11:41 AM
You only use the "or" keyword inside of the where clause. Something like this:


select a, b or c from table where b=1 or c=2

is not valid. Instead, use this:


select a, b, c from table where b=1 or c=2

Also make sure to group your conditions accordingly.


where a=1 or b=2 and c=3

is different than


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

newphpcoder
02-21-2012, 01:22 AM
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:





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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum