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?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.