5

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_QUERY error. 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 BY statement so that I can return a list of unique values, as SOQL does not use the DISTINCT keyword.

2 Answers 2

4

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.

For example:

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 FROM User WHERE Id IN ( SELECT OwnerId FROM Widget__c 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--%'
)
0

Like Jenny explained you don't need the GROUP BY clause, so get rid of it.

Your inner select field (Parent.id) can't have more than one level of relationship so change it to ParentId (You don't need the "." dot).

Finally the "left operand 'Account.Id' also cannot have more than one level of relationship so change it to AccountId (Again you don't need the "." dot).

So your final query should look something like the following.

SELECT Name
FROM Contact
WHERE AccountId IN
(
    SELECT ParentId
    FROM Account
    WHERE Name LIKE '%--this--%' OR Name LIKE '%--that--%'
)

You must log in to answer this question.

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