Not In Clause

[dukect]

I have the query as below:

SELECT FAccount from T5
where FAccount not in (


select Account from t3)

I know FAccount # 424337466884 in T5 is not in T3, so the query should at least return 424337466884. But this query returns nothing.

Please help. Thanks in advance.

[ajetrumpet]

look at your field sizes in both your tables. they're both 20, but one is following your orders and one is not. the only difference is that T5 has Unicode Compression turned off. T5 is cutting off data to the length of the acct numbers. But T3 is giving you the extra spaces you deserve.

Click that line in design view and press F1 to read about it. That's probably the reason for the query error.

[dukect]

look at your field sizes in both your tables. they're both 20, but one is following your orders and one is not. the only difference is that T5 has Unicode Compression turned off. T5 is cutting off data to the length of the acct numbers. But T3 is giving you the extra spaces you deserve.

Click that line in design view and press F1 to read about it. That's probably the reason for the query error.

Thank you very much for your help.

I've turned Unicode Compression on both t3 and t5 off and trimed the data in T3, still receive the same result.

If I modify the query from not in to in, the result turns out correct.

Just don't know why, very strange.

[ajetrumpet]

if all else fails, use TRIM() to get rid of trailing and leading spaces in your data. Then you can use whatever subquery statement you want, and the blanks won't matter.

[dukect]

if all else fails, use TRIM() to get rid of trailing and leading spaces in your data. Then you can use whatever subquery statement you want, and the blanks won't matter.

I did try to trim both column in t3 and t5, can't get it work. Very frustrate.

Thanks.

[LillMcGill]

I is your nulls

SELECT T5.FAccount
FROM T5
WHERE (((T5.[FAccount]) Not In (select Account from t3 where t3.Account is not null)));

[ajetrumpet]

I did try to trim both column in t3 and t5, can't get it work. Very frustrate.

Thanks.

hmmmm...maybe that's because there is a distinct field size?

another alternative would be to use REPLACE() to get rid of the " " characters and replace them with "" characters. That surely should work!

at any rate, good luck with it.

[LillMcGill]

Is my previous post invisible?

Not In (select Account from t3 where t3.Account is not null)));

[dukect]

Is my previous post invisible?

That works. But do you know why?

Thank you very very much.

[LillMcGill]

NULL=Unknown
So basically, you were saying SELECT something WHERE not in a list that includes unknown.

Everything potentially = the mystery value.

That is my understanding of the trouble with tribbles. I mean the trouble with the NOT IN operator.

[dukect]

NULL=Unknown
So basically, you were saying SELECT something WHERE not in a list that includes unknown.

Everything potentially = the mystery value.

That is my understanding of the trouble with tribbles. I mean the trouble with the NOT IN operator.

Got it. Thank again.