Join Query on Range

[NSearch]

Is it possible to join two tables without having an exact match?

I have two tables:

Table_1:

Tracking_Number
Invoice_Number
Service_Code
Weight
Zone
Type

Table_2:

Service_Code
Weight_Low
Weight_High
Zone
Type


Rate

I can do a 1-to-1 match on Service_Code, Zone and Rate, but I am not sure how to match the weight to a range in Table_2.

Please see the attached files:

Table_1: Testing_v4.zip

Table_2: rates_cwt_v1.zip

Thanks for the assistance.

[ArviLaanemets]

SELECT t1.Tracking_number, t1.InvoiceNumber, ..., t2.Rate
FROM Table_1 t1 INNER JOIN Table_2 t2 ON t2.Service_Code = t1.ServiceCode AND t2.Zone = t1.Zone
WHERE t1.Weight BETWEEEN t2.Weight_low and t2.Weight_High

But I don't understand why you need the second table with current design, and what you'll return from 2nd table in query except rate! Isn't Service_Code unique in Table_2?
When not, then why not add Rate also into Table_1 and get rid of table Table_2?
When yes, then are fields Zone and Type in Table_1 abundant. And weight limits in Table_2 must be applied when value for Weight is entered into Table_1 - so it will always be between Weight_Low and Weight_High in Table_2 for given Service_code.

[NSearch]

Both tables are limited to simplify the post and attached data. There are 15+ more fields in table_1 and 5+ more fields in table_2. Table_1 will constantly have changing data, Table_2 is a fixed lookup table.

Thanks for the post.

[NoellaG]

Hi,
can't you add a where instruction to your query like:

WHERE [Weight] Between [Weight_Low] And [Weight_High] ?