An odd quandry.

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.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.googlegr oups.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.|||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.googlegr oups.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.|||(KyussWren@.gmail.com) writes:
> 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?

EXISTS and NOT EXISTS have wider applicability than so. You also need
EXISTS / NOT EXISTS when the condition involves more than one column.
IN + subquery is mainly something I use when I'm writing ad hoc-queries
and I'm lazy. In programming code I use EXISTS / NOT EXISTS 90% of
the time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm a big EXISTS fan. The NULLs here are to do with the column you chose in
your subquery. I wouldn't think of it like COALESCE. Basically, NULL <>
anything, even another NULL. An IN predicate can be broken down like this:

x IN (1, 2, 3 null)

... means:

x = 1 or x = 2 or x = 3 or x = null

So, if x is 1, 2 or 3, it will be true. If x is null, then the result is
false, since x is really unknown and not equal to anything.

Now consider this:

x NOT IN (1, 2, 3 null)

... means:

x <> 1 and x <> 2 and x <> 3 and x <> null

Google de Morgan's Law.

What if x is 4? All conditions must be met. It passes the first 3, but
fails on the last, since 4 <> null is unknown, and is treated as false.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146665188.587472.63170@.v46g2000cwv.googlegro ups.com...
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.googlegr oups.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.