|
|
Posted by Tom Moreau on 05/03/06 05:06
Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
WHERE ... NOT IN.
select
*
from
Filenametbl f
where not exists (select * from
document_link_staging dls
where dls.key2_value = f.pickno)
and not exists (select * from
document_link_storage dls
where dls.key2_value = f.pickno)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@gmail.com> wrote in message
news:1146621327.262902.130050@v46g2000cwv.googlegroups.com...
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.
The three fields in question are identical.
Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value
And these three fields are populated from a substring of the filenames
which are generated in another table.
Filenametbl.pickno
here is the rub.
If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)
should return 200 records.
It returns 0 records.
So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)
returns 200 records in this scenario,
this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)
I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))
Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.
Navigation:
[Reply to this message]
|