Problem in SELECT Statement

Databases
1

i encountered problem in my select statement.

this is my query:


SELECT a.EMP_ID, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME, a.LOG_IN, a.LOG_OUT
 FROM hrdb.attendance AS a LEFT JOIN hris.employment em ON a.EMP_ID = em.EMP_NO
 WHERE DATE(LOG_IN) BETWEEN '2011-12-08' AND '2011-12-23'
 AND em.STATUS = 'Casual' OR LOG_IN IS NULL

the output of this query is all data from attendance table was displayed eventhough their STATUS is not Casual.

Thank you so much…

2

try:

SELECT
a.EMP_ID
, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME
, a.LOG_IN, a.LOG_OUT

FROM
    hrdb.attendance AS a
LEFT JOIN
    hris.employment AS em
        ON a.EMP_ID = em.EMP_NO
WHERE
    ((DATE(LOG_IN) BETWEEN '2011-12-08' AND '2011-12-23')
        AND
            em.STATUS = 'Casual')
    OR
        LOG_IN IS NULL
3

If you only want the rows with em.STATUS = ‘casual’ then use INNER JOIN instead of LEFT JOIN.

And when you use AND’s and OR’s in the WHERE clause, you’d better use brackets to prevend unexpected behaviour.
Based on what you wrote, I think it should be:


SELECT a.EMP_ID, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME, a.LOG_IN, a.LOG_OUT
FROM hrdb.attendance AS a
INNER JOIN hris.employment em
ON a.EMP_ID = em.EMP_NO
WHERE (    DATE(LOG_IN) BETWEEN '2011-12-08' AND '2011-12-23'
       OR    LOG_IN IS NULL )
AND em.STATUS = 'Casual'