Order of Operations in Where Clause

Question

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'
  )

Answer 1

0

With the AND both WHERE clauses have to evaluate to TRUE for a result to be returned.

answered Aug 16 '12 at 03:21 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

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)

Order of Operations in Where Clause

Follow this question

Related Questions