Not in Condition - 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 Condition

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2013-07-03 : 09:54:31

Hi All,

I tried to retrieved quotes where the quoteid not exist in filter table. I written the below queries but not returning anything,however there are many records.

SELECT * FROM quote
WHERE quote.QuoteId NOT IN (SELECT QUOTEID FROM Quotefilter)

select * from quote where not exists
(select QuoteId from Quotefilter
where quote.QuoteId = Quotefilter.QUOTEID )

Any help will be highly appreciated

Thanks
SG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-03 : 10:10:09

Are there any rows that satisfy the condition? Another way of testing it:

SELECT QuoteId  FROM quote 
EXCEPT
SELECT QUOTEID  FROM Quotefilter

Surya Alekhya
Starting Member

3 Posts

Posted - 2013-07-04 : 02:27:47

Both the queries what you have written are absolute..Please conform if there is any other column that has to be checked to get the result.

chbala85
Starting Member

49 Posts

Posted - 2013-07-04 : 02:36:54

SELECT colunms_list FROM quote
EXCEPT
SELECT colunms_list FROM Quotefilter

But colunms_list must be match.

chbala85
Starting Member

49 Posts

Posted - 2013-07-04 : 02:45:26

Hi your query will work but you should add
is null function in where clause if Quotedid having null values it will returns nulls that is the of problem your query.

SELECT * FROM quote
WHERE quote.QuoteId NOT IN (SELECT QUOTEID FROM Quotefilter where QUOTEID is Not null )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources