How could I write this....
Essentially if you look at the joins in this query..I have created two left joins that return a set of ContactAddress and a set of ClientAddress.
I need the select to return the ContactAddress first...but if the nid value of Contact is null return the ClientAddress.
I know I could do this column by column....but was wondering if their was a better way.
n1.nid,n1.nidClient, n1.Designate,n1.[Role],n1.[First Name],n1.[Last Name],n1.Salutation,n1.Title,
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone],
case when ContactAddressNid is null then
ClientAddress1 as [Address 1], ClientAddress2 as [Address 2], ClientAddress3 as [Address 3],
ClientAddressCity as [City], ClientAddressState as [State], ClientAddressZip as [Zip],ClientAddressCountry as [Country],
(Select ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry,
c.nid,c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title],
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c
inner join cadoc_system..tsitexcrmclient xc
on c.nidclient = xc.nidclient
inner join cadoc_system..tsite s
on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimaryAddress cp
on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
on ClientAddress.nId = ca.nIdAddress
c.cLastName not in('POOL','USER','ADMINISTRATOR') and s.cPSiteCode = 'Root')n1