2

I've been trying variations of this SQL query:

SELECT
  c.Id
, c.Email
, c.RecordTypeId
, o.CreatedDate
, o.Name
, o.StageName as Stage
, ca.Subject
, ca.Status 
FROM Contact_Salesforce c
JOIN Opportunity_Salesforce o ON c.ID = o.agent__c
JOIN Case_Salesforce ca ON c.ID = ca.contactid

to join data from 3 separate synchronized DEs. When I filter the DEs on the fields I'm targeting I return records that are closer to expected but when I'm using the SQL I'm only returning a fraction of that amount.

I'm using the contact as my ID and sendable Email address and the opportunities and Case to filter further. Am I missing something? Any workarounds for combining 3 synced DEs and then filtering from there?

1 Answer 1

2

When you use a JOIN clause, it is using an INNER JOIN where it will only return records that have matching values in both synchronized data extensions.

When you filter a synchronized data extension, you are only filtering on one data extension without matching any values from any other data extension. This will return a larger record size.

In your case, you are only returning matching values from Contact and Opportunity on ContactId = Agent__c (which is a custom field in Opportunity). Also doing returning matching values on Case and Contact on ContactId.

It could be very possible that not all of your Contacts are in the Opportunity and Case objects.

See the diagram below on JOIN statements.

JOIN statements

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .