Question on how to use OR in a Select statement with where clause

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

Code (markup):

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;

Code (markup):

but it did not work.Thank you

 

you cannot use OR in selecting fields, you can only use them after FROM and WHERE..

what you can do is select both fields then checked them via php..

 

You can only use OR in your WHERE clause.
And it's used like:

WHERE DATE(a.LOGOUT) = '2011-12-19' OR DATE(n.TIME_OUT) = '2011-12-19'

PHP:

Not sure if the structure you had in your WHERE clause works.

 

SELECT
FullName = LastName + ', ' + FirstName
FROM Employees
WHERE FullName = 'Bertrand, Aaron'

 

lol @ankushsharma

SELECT concat( <field1>, ' ', <field2> ) AS single FROM <your_table>

 

I revise my code suggested by other programmer.First Scenario: // the min_dtr has the 0000-00-00 00:00:00EMP_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:0009900215-000089----Dela Cruz, Juan A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 13:38:00and 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';

Code (markup):

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 correctand this is the second scenario: // the max_dtr has the 0000-00-00 00:00:00EMP_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:0000900392-000252----Dela Cruz, John A.-2011-12-20--2011-12-20 05:35:00--2011-12-20 15:38:00I 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';

Code (markup):

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:00Thank you so much

 

If you have 2 tables to check, use a UNION SELECT:

SELECT x, y, z FROM table1 WHERE condition = X

UNION SELECT x, y, w FROM table2 WHERE condition = V;

Code (markup):

x, y and z have to be the same types of fields. IOW, x can be numeric, char, date, whatever, but it has to be the same in both fields. Same with y, z and w. But they don't have to be the same fields, used for the same purpose, or in the same order in the tables. (If one table has a field the other one doesn't you can return a constant - SELECT NUL or SELECT 'X'.)

You'll get one row returned for each condition that matches one of the WHERE clauses. So if table1 has 2 rows that match and table2 has 3 rows that match, you'll get 5 rows. (You can even get the rows to tell you where they came from - SELECT x, y, z, 'table1' FROM table1 ... UNION SELECT x, y, w, 'table2' FROM table2 ...)