Find Accounts by Contact
A semi-join (Id IN (SELECT AccountId FROM Contact ...)) keeps parent rows that have a matching child.
Your task
Return the tagged Account that has a Contact with the wanted Email, then use NOT IN to return the tagged Accounts that do not.
String tag = 'AS-Q309-' + UserInfo.getUserId() + '-' +
String.valueOf(Datetime.now().getTime());
String tagPrefix = tag + '%';
List<Account> parents = new List<Account>{
new Account(Name = tag + '-Qualified'),
new Account(Name = tag + '-Other'),
new Account(Name = tag + '-Empty')
};
insert parents;
String emailToken = 'q' + String.valueOf(Datetime.now().getTime());
String matchingEmail = emailToken + '@example.com';
insert new List<Contact>{
new Contact(LastName = tag + '-Match', AccountId = parents[0].Id, Email = matchingEmail),
new Contact(LastName = tag + '-Other', AccountId = parents[1].Id,
Email = emailToken + '@other.example.com'),
new Contact(LastName = tag + '-Orphan', Email = matchingEmail)
};Requirements
- Semi-join
Id IN (SELECT AccountId FROM Contact ...)should be present - Anti-join
Id NOT IN (SELECT AccountId FROM Contact ...)should be present Semi count:should be1Semi matched:should betrueSemi nonmatch excluded:should betrueSemi empty excluded:should betrueAnti count:should be2