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.
| Author |
Topic |
|
johnd2323
Starting Member
1 Post |
 Posted - 2013-11-21 : 13:46:03
|
| hi,i'm a veteran sql developer yet i'm finding a seemingly simple query brutally difficult. I have two tables, both of which have fieldx in it, both as varchar(30). Table1 has a record with a fieldx value of 'smith'. Table2 has no records with a fieldx value of 'smith'. however, when I run this query:select * from table1 where fieldx not in ( select fieldx from table2 )I get an empty result set! the only thing I can think is that there's something like a global setting in sql i'm unaware of that is set to a value that could account for this. any idea why I wouldn't get the expected result set with a record in it? thank you kindly.- john |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-21 : 13:59:40
|
This works for me:;with table1 (fieldx) as (select 'smith'),table2 (fieldx) as (select 'jones')select * from table1 where fieldx not in ( select fieldx from table2 )OUTPUT:fieldx------smith No global setting. I assume you've confirmed 'smith' is not in table2?select * from table2 where fieldx = 'smith'Is the collation case insesitive? does ether table's smith value contain leading or trailing whitespace?Be One with the OptimizerTG |
 |
|
|
mhorseman
Starting Member
44 Posts |
Posted - 2013-11-22 : 03:40:29
|
| Have you got any NULLs in Table2?Amending TG's query a little to have a null, I get no output.;with table1 (fieldx) as (select 'smith'),table2 (fieldx) as (select 'jones' union select null)select * from table1 where fieldx not in ( select fieldx from table2 )Mark |
|
|
|
mhorseman
Starting Member
44 Posts |
Posted - 2013-11-22 : 03:40:57
|
| Have you got any NULLs in Table2?Amending TG's query a little to have a null, I get no output.;with table1 (fieldx) as (select 'smith'),table2 (fieldx) as (select 'jones' union select null)select * from table1 where fieldx not in ( select fieldx from table2 )Mark |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-22 : 07:13:47
|
quote:
Originally posted by johnd2323
hi,i'm a veteran sql developer yet i'm finding a seemingly simple query brutally difficult. I have two tables, both of which have fieldx in it, both as varchar(30). Table1 has a record with a fieldx value of 'smith'. Table2 has no records with a fieldx value of 'smith'. however, when I run this query:select * from table1 where fieldx not in ( select fieldx from table2 )I get an empty result set! the only thing I can think is that there's something like a global setting in sql i'm unaware of that is set to a value that could account for this. any idea why I wouldn't get the expected result set with a record in it? thank you kindly.- john
is your collation case sensitive?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-22 : 10:15:16
|
| If it is because of the NULLs then change your query to this:select t1.*from table1 t1left outer join table2 t2 on t2.fieldx = t1.fieldxwhere t2.fieldx is nullBe One with the OptimizerTG |
|
|
|
|
|
|
|
|
odd query result