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 '12 at 03:01 PM in Default

sqlLearner 1 gravatar image

sqlLearner 1
772 28 37 39

What datatype are you using for your date columns?
Aug 17 '12 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 '12 at 03:21 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

But OR will return too many
Aug 16 '12 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 '12 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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816
x977
x11
x3

asked: Aug 16 '12 at 03:01 PM

Seen: 824 times

Last Updated: Aug 17 '12 at 12:35 PM