right way to use OR clause in a where statement

[timscronin]

I have the below where clause on a select

where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and ((pa2.df_institutionname not like ('ACT%')
or pa2.df_institutionname not like ('county%')))

It returns records that have either one of the not like statements. If I comment out the line and use below it will work for one of the statements

where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and pa2.df_institutionname not like ('ACT%')

How can I get the two to work together. I thought the parenthesis would work but no luck.

 

[ccuster]

I may be misunderstanding but if you do not want wither Act or County in the pa2.df_institutionname, use an AND in replace of the OR

 

[timscronin]

Thanks, now I know why I am a DBA and not a math teacher

 

[SQLBill]

I think you need to change the OR to AND.

Here's what I think is happening, when this portion is run,

and ((pa2.df_institutionname not like ('ACT%')
or pa2.df_institutionname not like ('county%')))

it works like this: Let's say there's an institutionname of countyz. It is checked against the first part of the where ((pa2.df_institutionname not like ('ACT%') and comes up true. It's NOT LIKE ('ACT%'), so that is retrieved. The OR part NEVER comes into use. The OR is only used when the first part is not met. Like this:

Let's say there's a name of ACTION. It is checked against the first part and comes up FALSE - it IS like 'ACT%' so then it drops to the second part of the statement and comes up true - it is NOT like 'country%'.

So, in short, if you want BOTH parts to be considered, it needs to be AND.

where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and (pa2.df_institutionname not like ('ACT%')
and pa2.df_institutionname not like ('county%'))

-SQLBill