ApexSensei

Drills SOQL queries

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

  1. Semi-join Id IN (SELECT AccountId FROM Contact ...) should be present
  2. Anti-join Id NOT IN (SELECT AccountId FROM Contact ...) should be present
  3. Semi count: should be 1
  4. Semi matched: should be true
  5. Semi nonmatch excluded: should be true
  6. Semi empty excluded: should be true
  7. Anti count: should be 2

Sign in to open this drill