query not working as expected

[wilsoc31]

does anyone know why this query isnt working?  it would work i think ok in oracle so not sure what im doing wrong here... thanks in advance

select * from pool_play where tournament_id = '3522' and user_id= '1' and (team_name is not null or team_name !='' or team_name NOT LIKE '%[teamname]%');

 

i would expect all the or's to work inside the ()

 

so obviously i would expect nulls to filter out, and also the NOT LIKE '%[teamname]%  to filter out too

 

[gizmola]

It would not work any better in Oracle, because your logic is faulty.

Keep in mind that inside your parens, if any of those items are TRUE, then the entire group is true.

team_name is not null or team_name !='' or team_name NOT LIKE '%[teamname]%')

In the row you don't expect, consider each condition

team_name is not null ---> true

team_name !='' ---> true

team_name NOT LIKE '....'  ---> false.

So you get the row, despite the fact that the 3rd condition is false.  What it appears you really want is:

team_name is not null AND team_name !='' AND team_name NOT LIKE '%[teamname]%')