Drills › Query the org (SOQL)

Find Accounts by Contact

Query the org (SOQL) · Intermediate · 15 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A campaign selector needs Accounts that have a qualifying Contact, and a follow-up list of Accounts that don't, without loading every Contact.

Use IN and NOT IN with a Contact subquery to find tagged Accounts that do and don't have a matching Contact.

What you will learn

A semi-join keeps parent records that have a matching child. The subquery returns one lookup field, such as AccountId.

SELECT Id FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE Email = :wanted)

NOT IN flips it (an anti-join): Accounts with no matching Contact.

Rules. The subquery selects one Id or lookup field from another object. It can't use ORDER BY or LIMIT or be joined with OR, and one WHERE can hold two at most.

The task

Return the tagged Account that has a Contact with the wanted Email, then use NOT IN to return the tagged Accounts that don't.

Examples:

• only Qualified has a Contact with the wanted Email → Semi count: 1 and Semi matched: true
• Other (different Email) and Empty (no Contacts) → left out by IN, returned by NOT IN → Anti count: 2
• the orphan Contact has the Email but no Account → adds no Account

Requirements:

1. Keep both outer queries to this run with Name LIKE :tagPrefix.
2. Filter Account Id with IN and a Contact subquery that selects AccountId for matchingEmail.
3. Run the same filter with NOT IN in a second query.
4. Print the IN count, the three IN checks, and the NOT IN count. Print Semi count:, Semi matched:, Semi nonmatch excluded:, Semi empty excluded: and Anti count:.

Starter code

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)
};

List<Account> matches = new List<Account>();
List<Account> nonMatches = new List<Account>();
System.debug('Semi count: ' + matches.size());
System.debug('Semi matched: false');
System.debug('Semi nonmatch excluded: false');
System.debug('Semi empty excluded: false');
System.debug('Anti count: ' + nonMatches.size());

When it passes

IN returns only Qualified. NOT IN returns Other and Empty. The orphan Contact adds no Account to either list.

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Load Account Contacts · Summarize Opportunity amounts ›