1

I have the below SQL in Marketing Cloud. I want to stop a new line being entered into my data extension if it contains one of two RecordTypeId's. Thanks in advanced!

SELECT 
Contacts.Id, 
Accounts.Name AS Company, 
Contacts.Email AS EmailAddress, 
Contacts.FirstName, 
Contacts.LastName, 
Contacts.Name, 
Contacts.HasOptedOutOfEmail,
Contacts.Marketing_Selling__c AS Selling,
Contacts.Marketing_Buying__c AS Buying, 
Contacts.Marketing_Renting__c AS Renting, 
Contacts.Marketing_Auctions__c AS Auctions, 
Contacts.Marketing_Mortgages__c AS Mortgages, 
Contacts.Marketing_Equity_Release__c AS 'Equity Release', 
Contacts.Marketing_Pensions__c AS Pensions, 
Contacts.Marketing_Insurance__c AS Insurance, 
Contacts.Marketing_Property_Management__c AS 'Property Management',
Contacts.Marketing_News_Competitions__c AS 'News and Competitions',
Contacts.OwnerID AS OwnerID,
Contacts.pba__Office_pb__c AS OfficeID,
Contacts.MobilePhone AS MobilePhone,
Contacts.et4ae5__HasOptedOutOfMobile__c AS HasOptedOutOfMobile,
Contacts.Buyer_Status__c AS BuyerStatus,
Contacts.et4ae5__Mobile_Country_Code__c AS MobileCountryCode,
Contacts.RecordTypeId AS RecordTypeId
FROM Contact_Salesforce Contacts
LEFT JOIN Account_Salesforce Accounts ON Accounts.Id = Contacts.AccountId
WHERE Contacts.HasOptedOutOfEmail = 0
2
  • 2
    Add one more condition to your filter: WHERE ... AND (Contacts.RecordTypeId != ‘123’ OR Contacts.RecordTypeId != ‘456’)
    – zuzannamj
    Mar 4 '21 at 17:22
  • 1
    Or could even do a NOT IN if you are feeling fancy. ...AND Contacts.RecordTypeID NOT IN ('123','456'). There is some processing considerations to account for but wanted to offer it up as an alternative as well Mar 4 '21 at 19:36
1

As @zuzannamj said,

is SQL, a list of rows are returned. You can filter the list of rows returned in the WHERE conditions.

Currently you added:

WHERE Contacts.HasOptedOutofEmail = 0

so the engine will only generate rows where that condition is true.

To add additional filters you can use AND OR logic, so if you only want to show records that dont have a RecordTypeId of 123 than you can just add a line to your SQL query:

AND Contacts.RecordTypeId != '123'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.