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.

Continue reading...