not in clause not working

Question

Post reply

not in clause not working

Patrick.I

Hall of Fame

Points: 3105
More actions
November 10, 2008 at 8:27 pm

#72247

I can't get the below query to work 🙁
select distinct(area) as
from Stores where state
not in ('TX - Area 1')
It won't filter out the TX - Area 1.
Any ideas I have tried replace with no LUCK:doze:
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Check your data.

Something makes the values not the same.

Double spaces, space in front, line feed at the end of the fields values, anything else what could make the difference.

_____________
Code for TallyGenerator

tosscrosby SSCoach Points: 18045 More actions · Answer 2

Patrick Ige (11/10/2008)
select distinct(area) as
from Stores where state
not in ('TX - Area 1')

Something tells me there is more to the code than what you've posted. In my systems, state would never be = "TX - Area 1", "TX" sure but not with other info as the state code. Is there another select clause fulfilling the "not in" filter?

-- You can't be late until you show up.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Patrick Ige (11/10/2008)
select distinct(area) as
from Stores where state
not in ('TX - Area 1')

Why are you using "not in" here instead of "<>" or "!="?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Patrick.I Hall of Fame Points: 3105 More actions · Answer 4

THanks guys but what i need is

not in ('TX','NY,'MN')

I want to remove some more...

Will != or <> permit this?

rbarryyoung (11/11/2008)
Patrick Ige (11/10/2008)
select distinct(area) as
from Stores where state
not in ('TX - Area 1')
Why are you using "not in" here instead of "<>" or "!="?

tosscrosby SSCoach Points: 18045 More actions · Answer 5

Patrick Ige (11/12/2008)
THanks guys but what i need is
not in ('TX','NY,'MN')
I want to remove some more...
Will != or <> permit this?

"Not in" will work. Don't use != but you can use <> if you're comparing a single value. As I stated before, post your actual code as what you've posted so far does not appear to be the code you're really trying to execute. It's only going to lead to a more valid response.....

-- You can't be late until you show up.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

I wasn't aware that there was a difference between "!=" and "<>"?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

tosscrosby SSCoach Points: 18045 More actions · Answer 7

rbarryyoung (11/12/2008)
I wasn't aware that there was a difference between "!=" and "<>"?

There isn't but isn't "!=" "old school"??? I haven't used it for about ten years! 😀

-- You can't be late until you show up.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

tosscrosby (11/12/2008)
rbarryyoung (11/12/2008)
I wasn't aware that there was a difference between "!=" and "<>"?
There isn't but isn't "!=" "old school"??? I haven't used it for about ten years! 😀

Hmm, I checked and apparently it is not Standard SQL, which I did not know. (pretty bad, considering how I push standards!) :blush:

Still, I will probably use it here, because the angle-bracket cleaner on this site will leave "!=" alone.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]