Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial