>>SELECT DISTINCT table1.section_id, table2.lane_number FROM data\table1 ;
>> INNER JOIN data\table2 ON table2.element = table1.element ;
>> WHERE ((table2.beg_odometer>=table1.beg_odometer AND table2.beg_odometer<=table1.end_odometer) OR ;
>> (table2.iri_end_odometer>=table1.beg_odometer AND table2.end_odometer<=table1.end_odometer)) AND ;
>> table2.survey_year = cYear INTO TABLE data\my_test DATABASE data\temp
>>
>>I am just wondering if this can be refined to run faster, or if there is some
>>other better approach.
>
>How about:
>SELECT DISTINCT table1.section_id, table2.lane_number FROM data\table1 ;
> INNER JOIN data\table2 ON table2.element = table1.element ;
> WHERE (table2.beg_odometer BETWEEN table1.beg_odometer AND table1.end_odometer) OR ;
> (table2.end_odometer BETWEEN table1.beg_odometer AND table1.end_odometer)) AND ;
> table2.survey_year = cYear ;
> INTO TABLE data\my_test DATABASE data\temp
>
>?
Thanks, Garrett. Moving the odometer comparisons from the join condition to the where actually slowed it down, but the BETWEEN ... AND is good to know. It's a little faster than the >=/<=, and makes it easier to read the code.
-Bridget
Bridget K. Dawes