SQL CASE STATEMENT

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.

select
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],
n1.[Native/Linked],n1.ContactStatus
else


from

--native
(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
      
      
WHERE
  c.cLastName not in('POOL','USER','ADMINISTRATOR')  and s.cPSiteCode = 'Root')n1
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this

ISNULL(ContactAddressNid,ClientAddress1) as [Address 1]

select 
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], 
ISNULL(ContactAddressNid,ClientAddress1) as [Address 1], ClientAddress2 as [Address 2], ClientAddress3 as [Address 3],
ClientAddressCity as [City], ClientAddressState as [State], ClientAddressZip as [Zip],ClientAddressCountry as [Country],
n1.[Native/Linked],n1.ContactStatus
from
--native
(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
      
      
WHERE 
  c.cLastName not in('POOL','USER','ADMINISTRATOR')  and s.cPSiteCode = 'Root')n1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.