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:

  1SELECT Id, Name, Account.Name
  2FROM Contact 
  3WHERE Account.Industry = 'media'

  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 standard object subqueries, you should specify the plural name of the object as that is the name of the relationship for each object.

  For example:

  1SELECT Name,
  2  (
  3    SELECT LastName
  4    FROM Contacts
  5  )
  6FROM Account

  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:

  1SELECT Name,
  2  (
  3    SELECT CreatedBy.Name
  4    FROM Notes
  5  )
  6FROM Account

  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:
  1SELECT Amount, Id, Name,
  2  (
  3    SELECT Quantity, ListPrice,
  4 PricebookEntry.UnitPrice, PricebookEntry.Name 
  5    FROM OpportunityLineItems
  6  )
  7FROM Opportunity

  Using the same query, you can get the values on Product2 by specifying the product family (which points to the field's data):

  1SELECT Amount, Id, Name, (SELECT Quantity, ListPrice,
  2  PriceBookEntry.UnitPrice, PricebookEntry.Name,
  3  PricebookEntry.product2.Family FROM OpportunityLineItems)
  4  FROM Opportunity

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

  1SELECT Name,
  2  (
  3    SELECT LastName
  4    FROM Contacts
  5    WHERE CreatedBy.Alias = 'x') 
  6 FROM Account WHERE Industry = 'media'

  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.'