Order of Operations in Where Clause

I have a query in which I have multiple clauses in the where clause:

When seperate everything works correctly but when I add in the AND operator I am not getting the correct result:

Am I missing something?

    SELECT *

dbo.table1 a1 JOIN dbo.table2 a2 ON a1.[ID]=a2.ID

    LEFT JOIN dbo.table3 a3

    ON a2.ID=a3.IDNumber

    WHERE --Part 1
   ISNULL(a1.Date1,'')<>''  AND (CONVERT(DATETIME,a1.Date2)<> CONVERT(DATETIME,a1.Date1)))  

  --Part 2
 A3.IDNumber NOT IN ( SELECT a8.IDNumber FROM dbo.Table5 A8
  JOIN dbo.Table4 A9
  ON a8.IDNumber = A9.[ID] AND 
 WHERE A8.Status = 'Exit'
more ▼

asked Aug 16, 2012 at 03:01 PM in Default

sqlLearner 1 gravatar image

sqlLearner 1
802 36 44 47

What datatype are you using for your date columns?
Aug 17, 2012 at 12:35 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
With the AND both WHERE clauses have to evaluate to TRUE for a result to be returned.
more ▼

answered Aug 16, 2012 at 03:21 PM

Blackhawk-17 gravatar image

11.9k 28 31 37

But OR will return too many
Aug 16, 2012 at 03:22 PM sqlLearner 1
SQL Server returns what you ask for. Why do you feel you aren't getting the correct result with AND?
Aug 16, 2012 at 03:26 PM Blackhawk-17

Because It is only Returning the IDNumber that are not in the subquery in part 2 of my where clause...It is ignoring the part 1 of my where clause...

I want Part 1 which is when the 2 dates arent equal but then of those dates that arent equal the IDNUmber cant be in the subquery of part 2
Aug 16, 2012 at 03:32 PM sqlLearner 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 16, 2012 at 03:01 PM

Seen: 1083 times

Last Updated: Aug 17, 2012 at 12:35 PM