In and Not In problems... 1

[omacron]

I am write what is an easy script but for some reason I can't get it to work.

What i want to do is find all the customers that have not made sales. So here is my data:

Cust: ID Name
123 Bob
124 Mike
125 John

Sales: Bill ID
1 123
2 125


Results: ID Name
124 Mike

This is what I wrote:

select id, name
from cust
where id not in (select id from sales)

This comes up with nothing but if Run this:

select id, name
from cust
where id in (select id from sales)

Gives me the results:

Results: ID Name
123 Bob
125 John

As you can see this is the oposite to what i want. Just can't figure out why the IN would work and not the NOT IN.


thanks

 

[donutman]

select id, name
from cust
where not (id in (select id from sales))

Think of it that way.
-Karl

 

[donutman]

Hmm. It should work either way. It does on my tables. (SQL 2k)
-Karl

 

[swampBoogie]

Probably records with null in sales table.

 

[tektipdjango]

THE BEST RESULT FOR SPEED IS NOT WITH A SUBQUERY? BUT WITH A JOIN

SELECT custumer.id,name FROM
custumer
left join
sales
on custumer.id=sales.id
where sales.id is null

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​