B
Bret
Hi all,
I have two tables...
One with a complete list of addresses and road names (32,000)
The other with a list of addresses and road names as follows (1,500)
Either a pair of records with the same road name, but one is marked
as the high address and the other is marked as low, with the
corresponding address.
Or a single record with the road name, and the only address for that
road
marked as the only address
The first table has many more road names, and addresses than the other
table.
What I am trying to do is...
Select all the records from the first table that match by road name AND
who's address falls in the range of the second tables high and lows,
inclusive.
or who's address equals the only address for that road.
[TABLE 1]
ADDRESS | ROAD_NAME | OTHER FIELDS FOLLOW
27000 WALNUT WAY
27001 WALNUT WAY
27002 WALNUT WAY
..
OTHER ROAD
..
11980 MAPLE STREET
..
OTHER ROADS
..
12001 MAPLE STREET
12200 MAPLE STREET
..
OTHER MAPLE ADDRESSES
..
13100 MAPLE STREET
ETC
[TABLE 2]
ADDRESS | ROAD_NAME | HI_LOW
12000 MAPLE STREET L
13098 MAPLE STREET H
27002 WALNUT WAY O
18002 FIR DRIVE L
18700 FIR DRIVE H
21980 WHITE STREET H
20782 WHITE STREET L
I would want to return
27002 WALNUT WAY
12001 MAPLE STREET
12200 MAPLE STREET
UP TO
13098 MAPLE STREET
so on and so on
Anyone? I need to run this tomorrow morning (thur 04/05/2007)
I spent way too much time cleaning up bad road names in TABLE 1
Not very much experience with joins or subqueries.
I can return all the road name matches, but I am having trouble restricting
the results to within the ranges.
Thanks in advance for any tips and examples
Bret
I have two tables...
One with a complete list of addresses and road names (32,000)
The other with a list of addresses and road names as follows (1,500)
Either a pair of records with the same road name, but one is marked
as the high address and the other is marked as low, with the
corresponding address.
Or a single record with the road name, and the only address for that
road
marked as the only address
The first table has many more road names, and addresses than the other
table.
What I am trying to do is...
Select all the records from the first table that match by road name AND
who's address falls in the range of the second tables high and lows,
inclusive.
or who's address equals the only address for that road.
[TABLE 1]
ADDRESS | ROAD_NAME | OTHER FIELDS FOLLOW
27000 WALNUT WAY
27001 WALNUT WAY
27002 WALNUT WAY
..
OTHER ROAD
..
11980 MAPLE STREET
..
OTHER ROADS
..
12001 MAPLE STREET
12200 MAPLE STREET
..
OTHER MAPLE ADDRESSES
..
13100 MAPLE STREET
ETC
[TABLE 2]
ADDRESS | ROAD_NAME | HI_LOW
12000 MAPLE STREET L
13098 MAPLE STREET H
27002 WALNUT WAY O
18002 FIR DRIVE L
18700 FIR DRIVE H
21980 WHITE STREET H
20782 WHITE STREET L
I would want to return
27002 WALNUT WAY
12001 MAPLE STREET
12200 MAPLE STREET
UP TO
13098 MAPLE STREET
so on and so on
Anyone? I need to run this tomorrow morning (thur 04/05/2007)
I spent way too much time cleaning up bad road names in TABLE 1
Not very much experience with joins or subqueries.
I can return all the road name matches, but I am having trouble restricting
the results to within the ranges.
Thanks in advance for any tips and examples
Bret