I don't know if this is even possible, but here goes.

I have a name/address list which I'm running SQL against to pull a mailing
list; it contains 1.5 Million records.  I have been given a table containing
a list of about 500 street & city names which are to be excluded.

So, I have this SQL:

SELECT * FROM T WHERE ("CODE" = "R")

I need to modify this so that if the street field contains one of the
excluded streets within a given city, the record is NOT selected.  The
problem is that the street field also contains a street number, like 1234
Main Street, in Dallas, TX.

Any suggestions?

TIA

David

David,

<< I need to modify this so that if the street field contains one of the
excluded streets within a given city, the record is NOT selected.  The
problem is that the street field also contains a street number, like 1234
Main Street, in Dallas, TX. >>

The parsing of the street name may need a custom function in order to work
in a robust fashion.  However the rest is easy:

SELECT T.* FROM T
INNER JOIN EXCLUDE E ON T.CITY=E.CITY AND
GETSTREETNAME(T.STREET) <> GETSTREETNAME(E.STREET)
WHERE ("CODE" = "R")

An index on CITY in the T table will go a long way towards making this query
much faster, of course.

--
Tim Young
Elevate Software
www.elevatesoft.com