1

o See all opportunities (where field isclosed = false), contacts and tasks (where field isclosed = false) related to the account.

I Have This Select

Select Id, 
       (Select Id, WhoId FROM Tasks WHERE IsClosed = false), 
       (SELECT Id, AccountId FROM Contact WHERE IsClosed = false) 
FROM Opportunity 
WHERE IsClosed = false

I need to add contacts to the query but the semi join error "

false), (SELECT Id, AccountId FROM Contact WHERE IsClosed = false) ^ ERROR at Row:1:Column:93 Didn't understand relationship 'Contact' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names."

1 Answer 1

5

The part of your query where you're saying (Select Id, WhoId FROM Tasks WHERE IsClosed = false) is known as a Left Outer Join, or a Parent-Child Subquery. A semi-join is similar, but different (a semi-join is a subquery in the WHERE clause, not the SELECT clause).

In those subqueries, the name of the object you're targeting changes. You need to use the child relationship name. From Understanding Relationship Names (emphasis mine)

For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other objects, and has a relationshipName for each, Assets, Cases, and Contacts. These relationships can be traversed only in the SELECT clause, using a nested SOQL query. For example:

SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account

This query returns all accounts, and for each account, the first and last name of each contact associated with (the child of) that account.

The "plural of the child object name" rule doesn't hold in all cases. Custom relationships can have almost any child relationship name, and some standard relationships don't follow that rule.

e.g. if you have the "allow Contacts to be related to multiple Accounts" feature enabled, you get the AccountContactRelationship object. This object has two, standard master-detail relationship fields (one to Account, and one to Contact). If you're doing a parent-child subquery for this object from, let's say, Account, the child relationship name is AccountContactRelations instead of the AccountContactRelationships that we would expect.

In your case though, you have it easy. The child relationship name for Contact is Contacts. Simply add that 's' onto the end of the object name in that one subquery, and you should be fine.

+edit:

As Sebastian Kessel pointed out the isClosed field does not exist on the Contact object. While not cited as the cause of the error you're seeing, this would be the cause of the next error message you would see.

1
  • 1
    Is ‘isClosed’ a field in the Contacts object? That might be another failure. Feb 10, 2019 at 7:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .