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

avatar image

sqlLearner 1
972 39 51 57

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

avatar image

12k 30 35 42

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: 1409 times

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

Copyright 2016 Redgate Software. Privacy Policy