question

sqlLearner 1 avatar image
sqlLearner 1 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' )
sql-server-2008t-sqlwhereparentheses
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What datatype are you using for your date columns?
0 Likes 0 ·

1 Answer

· Write an Answer
Blackhawk-17 avatar image
Blackhawk-17 answered
With the **AND** both WHERE clauses have to evaluate to TRUE for a result to be returned.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But OR will return too many
0 Likes 0 ·
SQL Server returns what you ask for. Why do you feel you aren't getting the correct result with AND?
0 Likes 0 ·
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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.