...

View Full Version : Problem in SELECT Statement



newphpcoder
01-04-2012, 08:44 AM
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...

Old Pedant
01-04-2012, 09:24 AM
First of all, you no longer have a LEFT JOIN. Here is why:

http://www.codingforums.com/showthread.php?p=818192#post818192

Not that it matters, because from you comments it is obvious that you *REALLY* want an INNER JOIN! Why did you use LEFT JOIN if you don't understand what it does?

Secondly, you need to go back to elementary school arithmetic.

What is the value of

3 + 4 * 5

??

If you said 35, go back to school.

Muliplication has higher PRECEDENCE than addition.

The answer is 23.

TO get 35 you would do

( 3 + 4 ) * 5

Same with AND and OR.

So:


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'
AND ( em.STATUS = 'Casual' OR LOG_IN IS NULL )

Note the added parentheses there.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum