var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Not sure why this sql isn't working (NOT IN)
select fruit_name from FRUITS where fruit_name not in (select FRUIT_NAME from new_fruits)
say there are two tables
I want the sql to return bannanas and orange but the sql returns nothing
using sql server 2005
first, sql server problems should really be posted in the sql server forum
ANSI SQL solutions don't always work in every database system
secondly, you've obviously "dumbed down" your situation, beyond the point where it can be solved
the query you posted ~will~ return the results you expect -- bannanas and oranges
oh sorry didn't realize NOT IN was exclusive to only sql server? or to a couple of servers
i dumbed it down, since the tables contains thousands of rows(one table has 700k, other has 1000)
both use varchar(16) null for the column.
I don't know why NOT IN doesn't work,but using EXCEPT(T-sql) returns what I want as well.
select distinct fruit_name from FRUITS except
select distinct fruit_name from new_fruits
Last edited by fff398; 02-24-10 at 15:56.
Watch out for nulls!
If any FRUIT_NAME value is null in the new_fruits table, your query will return no rows at all.
Does this make any difference:
select fruit_name from FRUITS where fruit_name not in
(select FRUIT_NAME from new_fruits where FRUIT_NAME is not null)
dang!!! [ slaps forehead ]
i had forgotten about that particular whoopsie
nice one, jarlH
yup nulls in the table got me, and EXCEPT inheritantly ignored them
thanks for the help
Nulls just got me too!
Thankfully I found this old thread that explained what I was seeing, or I'd have spent hours trying to figure this out.