x

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 *
     FROM 

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)))  
 
  AND  
   --Part 2
  A3.IDNumber NOT IN ( SELECT a8.IDNumber FROM dbo.Table5 A8
   JOIN dbo.Table4 A9
   ON a8.IDNumber = A9.[ID] AND 
   CONVERT(VARCHAR(10),CONVERT(DATE,A8.StartDate),101)=CONVERT(VARCHAR(10),CONVERT(DATE,A9.Date2),101) AND 
   CONVERT(VARCHAR(10),CONVERT(DATE,A8.StartDate),101)=CONVERT(VARCHAR(10),CONVERT(DATE,A9.Date1),101)  
  WHERE A8.Status = 'Exit'
   )
more ▼

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

avatar image

sqlLearner 1
972 39 50 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

Blackhawk-17
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x2072
x1066
x17
x3

asked: Aug 16, 2012 at 03:01 PM

Seen: 1390 times

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

Copyright 2016 Redgate Software. Privacy Policy