Selecting fields from Contact based on Account subquery?

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.

Answer

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--%'
)

Attribution
Source : Link , Question Author : toolshed , Answer Author : Jenny B

Leave a Comment