Select .. NOT IN clause doesn't return anything? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Select .. NOT IN clause doesn't return anything?

Author

Topic

gbaia
Yak Posting Veteran

52 Posts

Posted - 2005-11-03 : 11:12:15

Hi there,

It's a very strange thing!
I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.

I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do:
select * from invoices where invoiceID = 302247 (and i'll get one result)
select * from customer_payments where invoice = 302247 (and i'll get none results)

however, if i do the following:
select * from invoices where invoice_id not in
(select invoice_id from customer_payments)
I get nothing!!!???

It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?

thank you so much for any help!

Grazi

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-03 : 11:52:48

Well you say invoice in one statement, and invoice_id in another....is that a typographical error?

Post he DDL for each table and read the hint link in my sig

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-03 : 12:09:29

Would there be NULL's in the resultset of the subquery...they could be an issue?

gbaia
Yak Posting Veteran

52 Posts

Posted - 2005-11-03 : 13:02:40

THANK YOU!! it was a null issue (as per the post of AndrewMurphy), so i've added 'isnull' and it worked fine! :-)

select * from invoices where invoice_id NOT in
(select isnull(invoice_id,0) from customer_payments)

Grazi

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 13:06:57

I don't suppose you have an invoice number 0, but if you did that would erroneously be excluded, so you might want to do:


select * from invoices where invoice_id NOT in
(select invoice_id from customer_payments WHERE invoice_id IS NOT NULL)

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-03 : 15:58:22

I also like


    SELECT l.* 
      FROM invoices l
 LEFT JOIN customer_payments
	ON l.invoice_id = r.invoice_id 
     WHERE r.invoice_id IS NULL

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

Kristen
Test

22859 Posts

Posted - 2005-11-06 : 07:55:07

"I also like ... LEFT JOIN"

Me too ... but I do wonder if its a bit harder for newbies to eyeball.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources