title: SQL JOIN value less than or equal to number
body: <p>I am trying to join two tables based on logic where each record from the first table would pull a value &lt;= 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.</p>
  
  <p>I have two tables. 'Table1' is the primary table</p>
  
  <pre><code>**Table1**
  +---------+---------+--------+
  | Product | Carrier | Weight |
  +---------+---------+--------+
  | Z       | B       | 600    |
  +---------+---------+--------+
  | Z       | B       | 350    |
  +---------+---------+--------+
  | Y       | A       | 150    |
  +---------+---------+--------+
  | X       | A       | 75     |
  +---------+---------+--------+
  | Y       | B       | 10     |
  +---------+---------+--------+
  | X       | A       | 40     |
  +---------+---------+--------+
  </code></pre>
  
  <p>'Table2' is the lookup table </p>
  
  <pre><code>**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 |
  +---------+--------+------+
  </code></pre>
  
  <p>The result would apply the closest cost from table2 for a weight &lt;= Table1</p>
  
  <pre><code>**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 |
  +---------+---------+--------+------+
  </code></pre>
  
  <p>Using a traditional join the first row delivers a cost as 600 is a weight listed in 'Table2'</p>
  
  <pre><code>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 |
  +---------+---------+--------+------+
  </code></pre>
  
  <p>I hope to make changes to the query above to achieved the desired result.</p>
  
  <p>FYI: I am using Microsoft SQL Server 2014.</p>