How to structure multiple WHERE criteria?

1

Hello

It's a bit puzzling to get a multiple WHERE criteria to work in the right order.

My query is:

WHERE [1] = "Y"
AND [2] NOT IN ("a","b")
AND [3] IN ("a","b","c")
AND (([4] IN ("a","b")
OR [4] IS NULL
OR [4] LIKE "A%")
AND [5] NOT IN ("a"))
OR ([4]="a"
AND [5]="a")

Basically, the WHERE and the next two AND will need to always be evaluated.
Then, in addition, I want to return:
([4] IN ("a","b") OR [4] IS NULL OR [4] LIKE "A%" AND [5] NOT IN ("a"))
OR
([4]="a" AND [5]="a")

However, I get results where [4] is not "a" AND [5]="a")

Any idea?

2

you know the drill by now. please provide sample data

declare @sql4 table([2] varchar(5), [3] varchar(5), [4] varchar(5))

insert into @sql4
select 'z', 'x', 'y'

etc etc etc

3

one idea is to use brackets

( col1 = 'A' AND col2 = 'B' )
AND
( col3 = 1 OR col4 = 4 )

Each bracket evaluates independantly
and then the AND is applied

there are rules how it works

2 Likes
4

sample data always helps

1 Like
5

sqlor

I will try to answer your question tomorrow

It's my bed time

6

I fixed it with some complex parenthesising, thanks.