Query too complex. How to code not like statement properly

[ynott]

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 van Stiphout]

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

[ynott]

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

[lylefair]

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?

[Tom van Stiphout]

On 22 May 2005 07:25:24 -0700, "ly******@yahoo.ca" <ly******@yahoo.ca>
wrote:

I haven't specifically tried the OP's example. Your suggestion is
also valid. I would hope both perform at about the same level.

-Tom.

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?

[David W. Fenton]

Tom van Stiphout <no*************@cox.net> wrote in
news:31********************************@4ax.com:

On 22 May 2005 07:25:24 -0700, "ly******@yahoo.ca"
<ly******@yahoo.ca> wrote:

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?

I haven't specifically tried the OP's example. Your suggestion is
also valid. I would hope both perform at about the same level.

Both solutions ignore the fact that the original question was about
a LIKE statement.

You could still do it with a Cartesian join using a WHERE clause
with LIKE.

SELECT DISTINCT Table1.*
FROM Table1, Table2
WHERE Table1.StreetName LIKE "*" & Table2.StreetName & "*";

The DISTINCT may or may not be required. I'm not exactly certain
about it, but I don't think it is.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[lylefair]

SELECT t1.TextValue
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.StreetName LIKE '*' & t6.StreetName & '*'
WHERE t6.StreetName Is Null;

[David W. Fenton]

"ly******@yahoo.ca" <ly******@yahoo.ca> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

SELECT t1.TextValue
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.StreetName LIKE '*' & t6.StreetName & '*'
WHERE t6.StreetName Is Null;

Well, I've checked the Jet query optimizer, and it optimizes joins
and equivalent WHERE clauses exactly the same way.

I'm also not sure what purpose is served with the Not Null
condition. Wouldn't an inner join accomplish the same thing?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[ynott]

Sorry, but I'm a bit confused. I'm not getting the expected results.
First off, I want to exclude all values that are in the second
table/field called t2.streetname. However, it's not working.
Instead, I'm seeing all values in table one.


On Mon, 23 May 2005 19:25:30 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"ly******@yahoo.ca" <ly******@yahoo.ca> wrote in
news:11**********************@g43g2000cwa.googleg roups.com:

SELECT t1.TextValue
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.StreetName LIKE '*' & t6.StreetName & '*'
WHERE t6.StreetName Is Null;

Well, I've checked the Jet query optimizer, and it optimizes joins
and equivalent WHERE clauses exactly the same way.

I'm also not sure what purpose is served with the Not Null
condition. Wouldn't an inner join accomplish the same thing?

[lylefair]

Which query did you use?
There are syntax errors in this one:
SELECT t1.TextValue
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.StreetName LIKE '*' & t6.StreetName & '*'
WHERE t6.StreetName Is Null;
It should be:
SELECT t1.TextValue
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.StreetName LIKE '*' & t2.StreetName & '*'
WHERE t2.StreetName Is Null;

[scterran2]

Yes, I noticed that it said t6 and change it yesterday. I'm still
getting the wrong values in the return. Anyway, here's a simplified
table to explain

SELECT T1.StreetName
FROM Table1 as t1
Left Join Table2 as t2
ON
t1.StreetName not LIKE '*' & t2.Streetname & '*';

Table1.StreetName
Main St
Jackson St
1st St

Table2.StreetName
Main St

Thus, 3 values in 1st table and only one in Second. With the Select
statement above (forget null for now to make this easier), I still get
all 3 streets returned, but I only get 2 streets (Jackson St and 1st St
since Main St was in the 2nd table and was suppose to be excluded in
the results.

Does that make sense?
TIA

[Arno R]

SELECT t1.StreetName
FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.StreetName LIKE '*' & t2.StreetName & '*'
WHERE t2.StreetName Is Null

OR (same query in fact, but other syntax)
SELECT Table1.StreetName
FROM Table1 LEFT JOIN Table2 ON Table1.StreetName LIKE '*' & Table2.StreetName & '*'
WHERE Table2.StreetName Is Null;

Arno R

<sc*******@hotmail.com> schreef in bericht news:11**********************@z14g2000cwz.googlegr oups.com...

Yes, I noticed that it said t6 and change it yesterday. I'm still
getting the wrong values in the return. Anyway, here's a simplified
table to explain

SELECT T1.StreetName
FROM Table1 as t1
Left Join Table2 as t2
ON
t1.StreetName not LIKE '*' & t2.Streetname & '*';

Table1.StreetName
Main St
Jackson St
1st St

Table2.StreetName
Main St

Thus, 3 values in 1st table and only one in Second. With the Select
statement above (forget null for now to make this easier), I still get
all 3 streets returned, but I only get 2 streets (Jackson St and 1st St
since Main St was in the 2nd table and was suppose to be excluded in
the results.

Does that make sense?
TIA