SQL server conditional join

Question

I have to choose the according column to join basing on a value that can be null. In detail:

SELECT shoporders.orderid, 
   shopaddresses.companyname, 
   shopaddresses.firstname, 
   shopaddresses.lastname, 
   shopaddresses.address1,  
FROM   shoporders 
   INNER JOIN shopaddresses 
           ON shoporders.InvoiceAddressId = shopaddresses.addressid
where orderid = 110
order by shoporders.createddate desc

if shoporders.InvoiceAddressId is null then I have to use shoporders.DeliveryAddressId

Any clue?

Thanks in advance

Answer 1

ON coalesce(shoporders.InvoiceAddressId, shoporders.DeliveryAddressId) = shopaddresses.addressid