Simple query just stopped working

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

Simple query just stopped working

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-23 : 04:11:50

Hello there,

can someone help before I smash my laptop up.

I have a simple query that was working yesterday, now its not.

select distinct occasionid
from Ticket_Facts
where OccasionID not in (select OccasionID from Game_Classification)

both occasioned columns are bigint types.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-04-23 : 04:14:34

...and the meaning of "not working" in this case is?
Please give us more information.

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 04:16:58

select distinct occasionid
from Ticket_Facts
where OccasionID not in (select OccasionID from Game_Classification WHERE OccasionID IS NOT NULL)

--
Chandu

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-23 : 04:25:37

thank you bandit

lovely

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 04:27:52

quote:
Originally posted by masterdineen

thank you bandi
lovely

welcome

Don't forget to include WHERE colName IS NOT NULL in subquery whenever you decided to use NOT IN

--
Chandu

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-23 : 04:32:50

why doesn't it just pick out the matching ids. even tho there is a null.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 04:38:25

NOT IN clause indirectly puts AND with multiple conditions
from Ticket_Facts
OccasionID not in (select OccasionID from Game_Classification)

i.e.
OccasionId !=ConditionValue1 AND OccasionId !=ConditionValue2 AND OccasionId !=ConditionValue3 ..... AND OccasionId != NULL

So Occasion != NULL returns always zero rows...

--
Chandu

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-04-23 : 05:22:37

o thank you for that, learn something new everyday

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 05:46:21

quote:
Originally posted by masterdineen

o thank you for that, learn something new everyday

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources