How to choose one column over another in a join if the column value is NULL?

Question

I am using sql server and using ANSI-89 standard.

1. I want to show the transport type name on each of the orderitems along with the item details
2. the transport code can be set at the order or individual orderitem.
3. The transport code on order item precedes the one on order table if its present.
4. If there is no transport code set on Order or OrderItem , i still need to retrieve the orderitem.

transport type is a join with the transport table on transporttypecode. the order or orderitem is joined with the transport table on transportcode.

How do I do a conditional join, if that is possible to get the transport type name for each order item, what is the right approach to use here so that I can pick the transport_code on order or order_item accordingly and join with transport table?

Order
-----
orderNo (PK)
country NOT NULL
transportCode NULL

OrderItems
----------
OrderNo (PK)
orderItemNo (PK)
transport_code NULL


Transport
---------
trasportcode (PK)
transportname NOT NULL
transporttypecode NOT NULL

transportType
-------------
transporttypecode (PK)
transporttypename NOT NULL

SAMPLE DATA

Order 
    OrderNo   Country  TransportCode
    1             USA       ST1
    2             ENGLAND       

Order Item
    Order No orderItemNo itemname transportCode
    1            1         cooker    ST2
    1            2         scissor
    2            1         stapler
    2            2         pencil    ST3
    2            3         bottle    

Transport
    transportcode           name        transporttypecode
    ST1                 INS BUTTERFLY           W
    ST2                 LUFTHANSA               A
    ST3                 TRANS SIBERIAN RAIL     RL

TransportType
    Transporttypecode name
    W                 Water
    A                 Air
    RL                Rail

Output

Order No orderItemNo itemname    transportName      transporttypeName
1            1         cooker    LUFTHANSA                Air
1            2         scissor   INS BUTTERFLY           Water 
2            1         stapler
2            2         pencil    TRANS SIBERIAN RAIL     Rail
2            3         bottle    

Answer 1

One method uses coalesce() in the on clause:

select oi.*, t.transportName, t.transporttypeName
from orderitems oi join
     orders o
     on oi.ordernum = o.ordernum left join
     transport t
     on t.transportcode = coalesce(oi.transportcode, o.transportcode) left join
     TransportType tt
     on tt.Transporttypecode = t.Transporttypecode;