Event is not supported for semi join inner selects

Question

I'm trying to get a phone number from an Account with a completed Event in SOQL. Here is my query

select Phone from Account where Name in (select School_Name__c from Event where Visit_Status__c = 'Completed')

I get an error that says

Entity 'Event' is not supported for semi join inner selects

Is this possible to do in one SOQL query?

Answer 1

No. You can only do a Left Inner Join on Id values anyway. Trying to join on Name would never work, regardless of which objects are involved. If you don't have too many Event records and you're in an Apex context, it wouldn't be an excessive amount of extra work/governor consumption to just loop through and collect the values:

Set<String> names = new Set<String>();
for (Event record : [
    SELECT TransAm_School_Name__c FROM Event
    WHERE TransAm_School_Name__c != null
    AND TransAm_School_Visit_Status__c = 'Completed'
]) names.add(record.TransAm_School_Name__c);
List<Account> schools = [SELECT Name FROM Account WHERE Name IN :names];