2005年5月21日星期六23:07:59 -0700，ynott< yn *** @ hotmail.com>写道：


将值放在表格中。然后写：

SELECT ... WHERE s.StreetName not in（select< FieldName> from

< NewTable>）


-Tom。

这里显然是新手。无论如何，我对如何解决这个问题感到有些困惑。我对我正在尝试创建的表进行查询。我正在尝试查看所有的streets.type值为null，但是
然后排除那些对于S.type而言为空的STREETNAME值，但是
确实没问题。查询如下：

SELECT S.ID，S.ID2，S.PREFIX，S.STREETNAME，S.TYPE
来自街道作为S
WHERE

S.STREETNAME不喜欢* HWY110 * AND
S.STREETNAME不喜欢* HWY111 * AND
S.STREETNAME不喜欢* HWY112 * AND
S.STREETNAME不喜欢* HWY113 * AND
S.STREETNAME不喜欢* HWY114 * AND
S.STREETNAME不喜欢* HWY115 * AND
S.STREETNAME不喜欢* HWY116 * AND
S.STREETNAME不喜欢* HWY117 * AND
S.STREETNAME不喜欢* HWY118 * AND
S.STREETNAME不喜欢* HWY119 *还有大约250多个值。

AND（（S.TYPE）Null））;

现在，一旦我到达大约50个左右这些值，我得到查询太复杂，我怎么能做到这一点？

TIA

完美。 Thx


2005年5月21日星期六23:24:29 -0700，Tom van Stiphout

< no ********* ****@cox.net>写道：

2005年5月21日星期六23:07:59 -0700，ynott< yn *** @ hotmail.com>写道：

将值放在表中。然后写：
SELECT ... WHERE s.StreetName not in（select< FieldName> from
< NewTable>）

-Tom。

这里显然是新手。无论如何，我对如何解决这个问题感到有些困惑。我对我正在尝试创建的表进行查询。我正在尝试查看所有的streets.type值为null，但是
然后排除那些对于S.type而言为空的STREETNAME值，但是
确实没问题。查询如下：

SELECT S.ID，S.ID2，S.PREFIX，S.STREETNAME，S.TYPE
来自街道作为S
WHERE

S.STREETNAME不喜欢* HWY110 * AND
S.STREETNAME不喜欢* HWY111 * AND
S.STREETNAME不喜欢* HWY112 * AND
S.STREETNAME不喜欢* HWY113 * AND
S.STREETNAME不喜欢* HWY114 * AND
S.STREETNAME不喜欢* HWY115 * AND
S.STREETNAME不喜欢* HWY116 * AND
S.STREETNAME不喜欢* HWY117 * AND
S.STREETNAME不喜欢* HWY118 * AND
S.STREETNAME不喜欢* HWY119 *还有大约250多个值。

AND（（S.TYPE）Null））;

现在，一旦我到达大约50个左右这些值，我得到查询太复杂，我怎么能做到这一点？

TIA

Tom


你试过这个吗？它慢吗？我找到了NOT IN非常，非常好b / b
慢。出于这个原因，我同意将值放入

表中，但我建议在SQL中使用JOIN和WHERE

CriteriaTable.StreetName为Null。 br />
但也许这个想法不再相关，SQL和JET或其他什么

会优化NOT IN条件，以便它的执行速度快？

Novice here obviously. Anyway, I''m a bit confused about how to
approach this. I have a query against a table that I''m trying to
create. I''m trying to view all streets.type values that are null, but
then exclude those STREETNAME values that are null for the S.type but
that are really okay. The query goes like:

SELECT S.ID, S.ID2, S.PREFIX, S.STREETNAME, S.TYPE
FROM streets as S
WHERE

S.STREETNAME Not Like "*HWY110*" AND
S.STREETNAME Not Like "*HWY111*" AND
S.STREETNAME Not Like "*HWY112*" AND
S.STREETNAME Not Like "*HWY113*" AND
S.STREETNAME Not Like "*HWY114*" AND
S.STREETNAME Not Like "*HWY115*" AND
S.STREETNAME Not Like "*HWY116*" AND
S.STREETNAME Not Like "*HWY117*" AND
S.STREETNAME Not Like "*HWY118*" AND
S.STREETNAME Not Like "*HWY119*" AND

etc for about 250 more values.

AND ((S.TYPE) Is Null));
Now, once I get to about 50 of these values or so, I get "query too
complex", how can I make this happen?

TIA

On Sat, 21 May 2005 23:07:59 -0700, ynott <yn***@hotmail.com> wrote:

Put the values in a table. Then write:
SELECT ... WHERE s.StreetName not in (select <FieldName> from
<NewTable>)

-Tom.

Novice here obviously. Anyway, I''m a bit confused about how to
approach this. I have a query against a table that I''m trying to
create. I''m trying to view all streets.type values that are null, but
then exclude those STREETNAME values that are null for the S.type but
that are really okay. The query goes like:

SELECT S.ID, S.ID2, S.PREFIX, S.STREETNAME, S.TYPE
FROM streets as S
WHERE

S.STREETNAME Not Like "*HWY110*" AND
S.STREETNAME Not Like "*HWY111*" AND
S.STREETNAME Not Like "*HWY112*" AND
S.STREETNAME Not Like "*HWY113*" AND
S.STREETNAME Not Like "*HWY114*" AND
S.STREETNAME Not Like "*HWY115*" AND
S.STREETNAME Not Like "*HWY116*" AND
S.STREETNAME Not Like "*HWY117*" AND
S.STREETNAME Not Like "*HWY118*" AND
S.STREETNAME Not Like "*HWY119*" AND

etc for about 250 more values.

AND ((S.TYPE) Is Null));
Now, once I get to about 50 of these values or so, I get "query too
complex", how can I make this happen?

TIA

Perfect. Thx

On Sat, 21 May 2005 23:24:29 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 21 May 2005 23:07:59 -0700, ynott <yn***@hotmail.com> wrote:

Put the values in a table. Then write:
SELECT ... WHERE s.StreetName not in (select <FieldName> from
<NewTable>)

-Tom.

Novice here obviously. Anyway, I''m a bit confused about how to
approach this. I have a query against a table that I''m trying to
create. I''m trying to view all streets.type values that are null, but
then exclude those STREETNAME values that are null for the S.type but
that are really okay. The query goes like:

SELECT S.ID, S.ID2, S.PREFIX, S.STREETNAME, S.TYPE
FROM streets as S
WHERE

S.STREETNAME Not Like "*HWY110*" AND
S.STREETNAME Not Like "*HWY111*" AND
S.STREETNAME Not Like "*HWY112*" AND
S.STREETNAME Not Like "*HWY113*" AND
S.STREETNAME Not Like "*HWY114*" AND
S.STREETNAME Not Like "*HWY115*" AND
S.STREETNAME Not Like "*HWY116*" AND
S.STREETNAME Not Like "*HWY117*" AND
S.STREETNAME Not Like "*HWY118*" AND
S.STREETNAME Not Like "*HWY119*" AND

etc for about 250 more values.

AND ((S.TYPE) Is Null));
Now, once I get to about 50 of these values or so, I get "query too
complex", how can I make this happen?

TIA

Tom

Have you tried this? Is it slow? I have found "NOT IN" to be very, very
slow. For this reason I would concur with putting the values into a
table, but I would suggest using a JOIN in the SQL and a WHERE
CriteriaTable.StreetName Is Null.
But perhaps that idea is no longer relevant and SQL and JET or whatever
will optimize the NOT IN condition so that its execution is fast?