SQL JOIN value less than or equal to number

Question

I am trying to join two tables based on logic where each record from the first table would pull a value <= to a number in the second table using a join. I am curious if this can be achieved in an efficient manner in SQL. I found some questions focused on dates, but nothing quite similar.

I have two tables. 'Table1' is the primary table

**Table1**
+---------+---------+--------+
| Product | Carrier | Weight |
+---------+---------+--------+
| Z       | B       | 600    |
+---------+---------+--------+
| Z       | B       | 350    |
+---------+---------+--------+
| Y       | A       | 150    |
+---------+---------+--------+
| X       | A       | 75     |
+---------+---------+--------+
| Y       | B       | 10     |
+---------+---------+--------+
| X       | A       | 40     |
+---------+---------+--------+

'Table2' is the lookup table

**Table2**
+---------+--------+------+
| Carrier | Weight | Cost |
+---------+--------+------+
| A       | 50     | 2.50 |
+---------+--------+------+
| A       | 100    | 2.00 |
+---------+--------+------+
| A       | 200    | 1.75 |
+---------+--------+------+
| B       | 200    | 1.85 |
+---------+--------+------+
| B       | 400    | 1.50 |
+---------+--------+------+
| B       | 600    | 1.35 |
+---------+--------+------+

The result would apply the closest cost from table2 for a weight <= Table1

**Result**
+---------+---------+--------+------+
| Product | Carrier | Weight | Cost |
+---------+---------+--------+------+
| Z       | B       | 600    | 1.35 |
+---------+---------+--------+------+
| Z       | B       | 350    | 1.50 |
+---------+---------+--------+------+
| Y       | A       | 150    | 1.75 |
+---------+---------+--------+------+
| X       | A       | 75     | 2.00 |
+---------+---------+--------+------+
| Y       | B       | 10     | 1.85 |
+---------+---------+--------+------+
| X       | A       | 40     | 2.50 |
+---------+---------+--------+------+

Using a traditional join the first row delivers a cost as 600 is a weight listed in 'Table2'

SELECT a.Product
      ,a.Carrier
      ,a.Weight
      ,b.Cost
FROM dbo.table1 a
LEFT JOIN Table2 b ON a.Carrier = b.Carrier AND a.Weight = b.Weight
+---------+---------+--------+------+
| Product | Carrier | Weight | Cost |
+---------+---------+--------+------+
| Z       | B       | 600    | 1.35 |
+---------+---------+--------+------+
| Z       | B       | 350    | NULL |
+---------+---------+--------+------+
| Y       | A       | 150    | NULL |
+---------+---------+--------+------+
| X       | A       | 75     | NULL |
+---------+---------+--------+------+
| Y       | B       | 10     | NULL |
+---------+---------+--------+------+
| X       | A       | 40     | NULL |
+---------+---------+--------+------+

I hope to make changes to the query above to achieved the desired result.

FYI: I am using Microsoft SQL Server 2014.

Answer 1

You should look for the first record that match the conditions:

SELECT a.Product
      ,a.Carrier
      ,a.Weight
      ,(select top 1 Cost from Table2 b
        where a.Carrier = b.Carrier AND a.Weight <= b.Weight
        ORDER BY b.Carrier, b.Weight ASC) Cost
FROM dbo.table1 a