SQL Query : Using JOINS with CASE Statement [closed]

Question

I've 3 DE

1. MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate
2. Staging DE with field CustomerID(PK), Createddate
3. Reference DE with field donottrack default value false.

SubscriberKey would be like - 10digit(numeric)_emailId and CustomerId - 10digit(numeric).

I'm able to solve the above problem statement with CROSS JOIN. Below is my Queries

    Select a.SubscriberKey,
b.Created_Date,
b.Last_Modified_Date,
CASE WHEN LEFT (a.SubscriberKey,10) = (b.Customer_ID) THEN b.DoNotTrack
     
     ELSE r.DoNotTrack

END as DoNotTrack
from ent.[STAGING_DE2] a
LEFT JOIN
ent.[MASTER_CUSTOMER_CONSENT_DATA] b
ON LEFT (a.SubscriberKey,10) = (b.Customer_ID)

CROSS JOIN ent.[ReferenceDE] r

But If I don't want to use CROSS JOIN, Just want to save Result of 1st half of SQL in DE and then again write 1 more SQL query to update the value of DonotTrack field with value from other DE. How could I do that?. It will look like something below,

1st SQL result stored in DE :

Select a.SubscriberKey,
b. DoNotTrack,
a.Created_Date,
a.Last_Modified_Date
from ent.[STAGING_DE2] a
LEFT JOIN
ent.[MASTER_CUSTOMER_CONSENT_DATA] b
ON LEFT (a.SubscriberKey,10) = b.Customer_ID

2nd SQL will update the same DE, but took value of DonotTrack from Reference DE, query I'm trying to build like - I'm able to solve this too, see my below query

Select a.SubscriberKey,
a.Created_Date,
a.Last_Modified_Date,
r.DoNotTrack as DoNotTrack

FROM ent.[RESULTING_DE] a

FULL JOIN ent.[ReferenceDE] r

ON 1=1

WHERE a.DoNotTrack = 'False'

Since I was not having any common field in above both DE, but I just want to reference the Donottrack value from Reference DE, I've applied above solution and it worked.

Answer 1

In the absence of details about ent.[ReferenceDE], here's my interpretation:

For all rows in Staging_DE2 that match a row in ent.[ReferenceDE] use the DoNotTrack value from ent.[ReferenceDE], otherwise use the DoNotTrack value from Staging_DE2.

Select 
  a.SubscriberKey
, b.Created_Date
, b.Last_Modified_Date
, case 
     when r.SubscriberKey is null then a.DoNotTrack
     else r.DoNotTrack
  end DoNotTrack
from ent.[STAGING_DE2] a
left join ent.[ReferenceDE] r ON r.SubscriberKey = LEFT(a.SubscriberKey,10)

Or it could be simpler with a COALESCE, which returns the first non-null value in from the specified fields/expressions:

Select 
  a.SubscriberKey
, b.Created_Date
, b.Last_Modified_Date
, coalesce(r.DoNotTrack,a.DoNotTrack) DoNotTrack 
from ent.[STAGING_DE2] a
left join ent.[ReferenceDE] r ON r.SubscriberKey = LEFT(a.SubscriberKey,10)