How to get a Postcode to show depending on if an AdhocID is NULL/NOT NULL

I am currently trying to return postcodes for delivery addresses. The issue i am currently experiencing is some of these are Ad hoc addresses so would not refer to the companies standard SiteID. Every record has a CompanySiteID and this is the only point i can JOIN ON between the Ordering Table and the Address Table.

What I want the data to do or if it can be done in the from clause is to say if the DeliveryCompanySiteID is blank then return the postcode for the AdHocDeliveryAddressID if not find the AddressID for the site and then find the postcode for that AddressID

Below is a sample of the tables and what i currently get:

Table 1 - order

CompanySiteID   DeliveryCompanySiteID   InvoiceCompanySiteID AdHocDeliveryAddressID
 8613552                                      8613552            663401931
94823142            326963198                326963198  
633057107           634312178                565578092

Table 2 - Address

AddressID   AddPostCode
663401931    NG1 1PQ
123          S12 1TP
456          S1 9PU
789          S11 1TY

Table 3 - company site

CompanySiteID   AddressID
8613552       123
94823142      456
633057107     789

Based on the code i am using below this is the result i am getting:

Site ID Site        PostCode
8613552             S12 1TP
94823142            S1 9PU
633057107           S11 1TY

The reason i believe this is happening is because of my JOIN which is:

Reason why I get the current result is because my join asks: JOIN SBS.PUB.Address Address_0 ON CompanySite_0.AddressID = Address_0.AddressID

Unfortunately there is no other way to join the tables, So i feel i need something in place that effectively says if the DeliveryCompanySiteID is blank then return the postcode for the AdHocDeliveryAddressID if not find the AddressID for the site and then find the postcode for that AddressID

Please see a simplified version of my code:

SELECT 
 CompanySite_0.CompanySiteID
    , Address_0.AddPostCode AS 'Delivery Post Code'


 FROM 

   SBS.PUB.SopOrderItem SopOrderItem_0 
    JOIN SBS.PUB.CompanySite CompanySite_0 ON SopOrderItem_0.CompanySiteID = CompanySite_0.CompanySiteID
    JOIN SBS.PUB.Address Address_0 ON CompanySite_0.AddressID = Address_0.AddressID

In the end i hope to be able to get to the position where based on the sample i showed above that the result should return as:

Site ID Site      PostCode
8613552           NG1 1PQ            
94823142          S1 9PU
633057107         S11 1TY

And not like:

Site ID Site     PostCode
8613552          S12 1TP 
94823142         S1 9PU
633057107        S11 1TY

I hope i have explained this well enough as i am struggling to explain it to myself at the moment.

Solution

I did it in SQL Developer, hopefully it'll work with the Openedge SQL interpreter (which uses SQL-92, last I checked).

SELECT
  orderitem.COMPANYSITEID
  , ADDRESS.ADDPOSTCODE as "Delivery Post Code"
  FROM 
    ORDERITEM
    LEFT JOIN COMPANYSITE ON (CASE WHEN ORDERITEM.DELIVERYCOMPANYSITEID IS NULL THEN 
                                        ORDERITEM.ADHOCDELIVERYADDRESSID ELSE 
                                        ORDERITEM.COMPANYSITEID END ) = COMPANYSITE.COMPANYSITEID 
    JOIN address ON (CASE WHEN companysite.addressID IS NULL THEN 
                               ORDERITEM.ADHOCDELIVERYADDRESSID ELSE 
                               companysite.addressID end ) = address.addressID;

That gave me the result

 94823142   S1 9PU
633057107   S11 1TY
  8613552   NG1 1PQ