SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Not in Condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satheesh
Posting Yak Master

United Kingdom
150 Posts

Posted - 07/03/2013 :  09:54:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3722 Posts

Posted - 07/03/2013 :  10:10:09  Show Profile  Reply with Quote
Are there any rows that satisfy the condition? Another way of testing it:
SELECT QuoteId  FROM quote 
EXCEPT
SELECT QUOTEID  FROM Quotefilter
Go to Top of Page

Surya Alekhya
Starting Member

3 Posts

Posted - 07/04/2013 :  02:27:47  Show Profile  Reply with Quote
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.
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 07/04/2013 :  02:36:54  Show Profile  Reply with Quote
SELECT colunms_list FROM quote
EXCEPT
SELECT colunms_list FROM Quotefilter

But colunms_list must be match.
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 07/04/2013 :  02:45:26  Show Profile  Reply with Quote

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 )
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000