asked
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' )
With the **AND** both WHERE clauses have to evaluate to TRUE for a result to be returned.
SQL Server returns what you ask for. Why do you feel you aren't getting the correct result with AND?
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
8 People are following this question.
