Using Relationship Queries

You can query the following relationships using SOQL:

• Query child-to-parent relationships, which are often many-to-one. Specify these relationships directly in the SELECT, FROM, or WHERE clauses using the dot (.) operator.

  For example:

  This query returns the ID and name for only the contacts whose related account industry is media, and for each contact returned, the account name.

• 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:

  The query returns the name for all the accounts, and for each account, the last name of each contact.

• Traverse the parent-to-child relationship as a foreign key in an aggregate query:

  For example:

  This query returns the accounts in an organization, and for each account, the name of the account, the notes for those accounts (which can be an empty result set if there were no notes on any accounts) with the name of the user who created each note (if the result set is not empty).

• In a similar example, traverse the parent-to-child relationship in an aggregate query:

  Using the same query, you can get the values on Product2 by specifying the product family (which points to the field's data):
• Any query (including subqueries) can include a WHERE clause, which applies to the object in the FROM clause of the current query. These clauses can filter on any object in the current scope (reachable from the root element of the query), via the parent relationships.

  For example:

  This query returns the name for all accounts whose industry is media, and for each account returned, returns the last name of every contact whose created-by alias is 'x.'