|
|
Posted by KyussWren on 05/03/06 17:06
Hey, whoa, that works.
You're the man Tom.
So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?
Tom Moreau wrote:
> 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]
|