what is the opposite of "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

what is the opposite of "in"

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2006-12-08 : 11:08:03

Hello!

I am looking for the oposit of the following where clause:

SELECT * FROM dbo.Element WHERE
(Element.Id in (Select Element_2_ID FROM dbo.Element_Element))

this is not working :

SELECT * FROM dbo.Element WHERE
not (Element.Id in (Select Element_2_ID FROM dbo.Element_Element))

Thank you very much for any feedback!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

Kristen
Test

22859 Posts

Posted - 2006-12-08 : 11:10:41

SELECT * FROM dbo.Element WHERE
(Element.Id NOT in (Select Element_2_ID FROM dbo.Element_Element))
OR Element.Id IS NULL

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 11:16:13

You can also use NOT EXISTS if you feel scary about handling NULLs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-08 : 11:27:30

I thought OUT was the opposite of IN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-08 : 11:32:07

No time for the ol' in-out luv.. i'm just here to read the meter.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 02:13:48

quote:
Originally posted by X002548

I thought OUT was the opposite of IN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ROTFL

Madhivanan

Failing to plan is Planning to fail

fabianus76
Posting Yak Master

191 Posts

Posted - 2006-12-09 : 11:13:03

Hello !

Excuse me for my late reaction, I forgot to ask for a feedback on reply...

I really do not understand why the way of Kisten does not work either.

Finally only this brought the solution :

SELECT *
FROM
dbo.Element_Element
RIGHT OUTER JOIN dbo.Element ON (dbo.Element_Element.Element_2_ID = dbo.Element.Id)
WHERE
(dbo.Element_Element.Id IS NULL)

Thanks for your help !

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources