Dear community,
I have a table of orders which contain information on weight, service and package type.
I want to join these to a rate table that has the service, package type and a upper and lower weight limit. Is there a way I can do a join that matches the service and package type then picks up the correct weight bracket if the weight is greater than the min and less than the max?
Example Below:
Table 1
| Booking | Weight | Service | Package Type |
|---|
| 1 | 5 | Express | Box |
| 2 | 10 | Economy | Parcel |
| 3 | 50 | Express | Parcel |
Table 2
| Supplier | Weight From | Weight To | Service | Package Type | Cost |
|---|
| Supplier A | 0 | 10 | Express | Box | £5 |
| Supplier B | 0 | 50 | Express | Box | £10 |
| | | | | |
What I want to do is add a column for each supplier so Table 1 would have the price for supplier A on line 1 as £5 and a column for supplier 2 for line 1 of £10
Many thanks - Drew
