HELP with and/or statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP with and/or statement

Author

Topic

NaeemK
Starting Member

10 Posts

Posted - 2011-11-15 : 06:48:45

Hi. I know that I should get a return of 47 records of countries that are not in the UK. However the below code does not pull them out. I think is it to do with the 'IS NOT NULL' part of the code.
Can anyone help?

SELECT T1.[GROUP_#], T1.Appeal_Code, add_country
FROM Naeem..RG_2011 T1
WHERE T1.Appeal_Code = 'ML04'
AND (t1.ADD_COUNTRY IS NOT NULL
OR t1.ADD_COUNTRY NOT IN('United Kingdom','','UK'))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-15 : 06:53:14

and isnull(t1.add_country,'') not in ('United Kingdom','','UK')

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 06:54:02

thsi is sufficient as NOT IN excludes NULL values by default


SELECT T1.[GROUP_#], T1.Appeal_Code, add_country
FROM Naeem..RG_2011 T1
WHERE T1.Appeal_Code = 'ML04'
AND t1.ADD_COUNTRY NOT IN('United Kingdom','','UK')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NaeemK
Starting Member

10 Posts

Posted - 2011-11-15 : 07:12:42

Ok, that's great.
So by including the NOT IN code causes it to not work correctly? Why does that happen? I understand that it isn't needed, however I am not sure why it doesn't with it there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 07:27:11

quote:
Originally posted by NaeemK

Ok, that's great.
So by including the NOT IN code causes it to not work correctly? Why does that happen? I understand that it isn't needed, however I am not sure why it doesn't with it there.

whats happening when you're using it? are you getting less rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NaeemK
Starting Member

10 Posts

Posted - 2011-11-15 : 07:50:33

When I use it, it returns all 966 rows for that appeal code. Of which 47 are non uk. When I omit the IS NOT NULL part it work fine. I don't understand why that is happening.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-15 : 09:03:29

Your first post shows us a NOT OR NOT and that's why it is happening.
not OR not on the same column is always true.
Do you understand?

No, you're never too old to Yak'n'Roll if you're too young to die.

NaeemK
Starting Member

10 Posts

Posted - 2011-11-15 : 10:06:30

Yes, of course. Doh!! Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources