I would like to return all related contact names based on a specific set of accounts returned via a subquery, but keep receiving a
MALFORMED_QUERYerror. Running the following SOQL in Workbench is yielding an error:
SELECT Name FROM Contact WHERE Account.Id IN ( SELECT Parent.Id FROM Account WHERE Name LIKE '%--this--%' OR Name LIKE '%--that--%' GROUP BY Parent.Id )
Note that I am using the
GROUP BYstatement so that I can return a list of unique values, as SOQL does not use the
A subquery would be if you flipped your query around and had something like the following example found on the help docs here:
Query parent-to-child, which are almost always one-to-many. Specify
these relationships using a subquery (enclosed in parentheses), where
the initial member of the FROM clause in the subquery is related to
the initial member of the outer query FROM clause. Note that for
subqueries, you should specify the plural name of the object as that
is the name of the relationship for each object.
SELECT Name, ( SELECT Name FROM Models__r ) FROM Widget__c
The query returns the name for all the Widgets, and for each Widget,
the name of each Model.
What you are attempting above is more like a semi-join as described here:
ID field Semi-Join
You can include a semi-join in a WHERE clause:
SELECT Id, Name
WHERE Id IN
WHERE Name = ‘Ring’
This is a parent-to-child semi-join from User to Widget. Notice that
the left operand, OwnerId, of the IN clause is an ID field. The
subquery returns a single field of the same type as the field to which
it is compared. A full list of restrictions that prevent unnecessary
processing is provided at the end of this section.
So basically, you do not need to worry about the distinct, since the returned ids are just used to filter the contacts you are getting.
Also noticed the you are using Account.Id and Parent.Id, try the following:
SELECT Name FROM Contact WHERE AccountId IN ( SELECT ParentId FROM Account WHERE Name LIKE '%--this--%' OR Name LIKE '%--that--%' )