NOT IN - 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

NOT IN

Author

Topic

pureclass85
Starting Member

29 Posts

Posted - 2009-03-08 : 15:26:54

could anyone tell me why when i run the following if there is more than one bird it will display it anyway

select a.common_name, a. family_name
from bird a
where (select count(*) from expectation
where a.common_name not in (expectation.bird_name);

basicly what i have been working on is to only display a birds name if it dosnt appear in the expectation table. if the expectation table has a name that is in there twice it will display that name... and i cannot stop it any ideas?

pureclass85
Starting Member

29 Posts

Posted - 2009-03-08 : 15:45:39

prob even more basicly i am trying to not display a name if it exists in a diff table. but if it dosnt exist in a coloum of a diff table then display it

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-08 : 16:02:56

[code]Select a.common_name,a.family_name
from birds a left outer join Expectation e
on a.common_name = e.birdname
Where e.birdname is null
UNION
Select a.common_name,a.family_name
from birds inner join expectations e
on a.common_name = e.birdname[/code]

pureclass85
Starting Member

29 Posts

Posted - 2009-03-08 : 16:27:11

indeed a flowing fount of yak knowledge, thank you sir!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 03:11:46

quote:

Originally posted by sodeep

Select a.common_name,a.family_name
from birds a left outer join Expectation e
on a.common_name = e.birdname
Where e.birdname is null
UNION
Select a.common_name,a.family_name
from birds inner join expectations e
on a.common_name = e.birdname

isnt this same as


Select  distinct a.common_name,a.family_name
from birds left outer join expectations e
on a.common_name = e.birdname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources