CODEWITHSUNDEEP
sqlsql-serversql-server-2005
mameesh
mameesh

Reputation: 3761

INNER JOIN on greatest value

I am wondering if it is possible to innner join on the greates value.

So for example

SELECT *
FROM Vehicle v
INNER JOIN VehicleCost vc ON v.VehicleWeight > vc.WeightFrom AND c.VehicleWeight < vc.WeightTo

But if v.VehicleWeightTotal > v.VehicleWeight I want that to replace v.VehicleWeight on the inner join. So potentially this could be a different join for each vehicle.

How would I go about doing that check within the join?

Sample:

tblVehicle
VehicleId  VehicleWeight VehicleWeightTotal 
1               12              15
2               1               8
3               16              20

tblVehicleCost
WeightFrom    WeightTo  Dollars
0               5         1
6               11        8
12              16        9 
17              20       15

So:

Vehicle 1 = 9
Vehicle 2 = 8
Vehicle 3 = 15

Upvotes: 3

Views: 162

Answers (3)

Andriy M
Andriy M

Reputation: 77657

You could use a derived table to define a column for the greater weight between VehicleWeight and VehicleWeightTotal as a CASE expression. That way you wouldn't have to repeat the long expression in your join condition:

WITH GreaterWeights AS (
  SELECT
    *,
    GreaterWeight = CASE
      WHEN VehicleWeightTotal > VehicleWeight THEN VehicleWeightTotal
      ELSE VehicleWeight
    END
  FROM Vehicle
)
SELECT *
FROM GreaterWeights v
  INNER JOIN VehicleCost vc ON v.GreaterWeight > vc.WeightFrom
                           AND v.GreaterWeight < vc.WeightTo

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Your requirements can be more simply expressed as:

use the greater of VehicleWeight and VehicleWeightTotal when joining

This is how you express that in SQL:

SELECT *
FROM Vehicle v
INNER JOIN VehicleCost vc 
   ON (case
         when VehicleWeightTotal > VehicleWeight then VehicleWeightTotal
         else VehicleWeight
       end) between vc.WeightFrom AND vc.WeightTo;

I took the liberty of changing your range check to use between, but if it's not OK to match on the value equalling the range limits - ie using >= and <= in your query - then just repeat the case statement for each side of the range.

Upvotes: 5

Derek
Derek

Reputation: 23228

SELECT *
FROM Vehicle v
INNER JOIN VehicleCost vc ON 
  case when v.VehicleWeight > v.VehicleWeightTotal then v.VehicleWeight
       else v.VehicleWeightTotal end > vc.WeightFrom 
  AND case when v.VehicleWeight > v.VehicleWeightTotal then v.VehicleWeight
       else v.VehicleWeightTotal end < vc.WeightTo

Upvotes: 4

Related Questions