This section discusses some of the features and restrictions that are specific to how the cmdlet supports JOINs.

If possible, the cmdlet attempts to perform JOINs server-side. JOINs that Certinia cannot process are performed client-side when SupportEnhancedSQL is set to true.

The CData Cmdlets PowerShell Module for Certinia supports server-side JOINs based on Certinia Object Query Language (SOQL). The cmdlet supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. JOIN queries in Certinia are based on the relationships among Certinia objects.

Relationship Queries

Certinia objects can be linked using relationships. The standard Certinia objects have predefined relationships. You can define relationships for your custom objects.

Parent to Child Relationships

Certinia relationships are directional and are of the following types: one-to-many (parent to child) or many-to-one (child to parent). Since the relationships are directional, the order in which the tables are included in the query determines the path of relationship traversal.

The following query shows a simple parent-to-child JOIN query. This query returns all Accounts and the first and last name of each Contact associated with that Account.

SELECT Contact.FirstName, Account.Name
FROM Account LEFT JOIN Contact ON Account.Id = Contact.AccountId

Polymorphic Relationships

Certinia relationships can be polymorphic. That is, a given relationship on a field can refer to more than one type of entity. For example, the Task entity contains a Who relationship, which, by default, may refer to a Contact or Lead.

The following query shows a JOIN based on a polymorphic relationship. This query returns all contacts and task information that relate to a contact.

SELECT Task.Subject, Contact.Name 
    FROM Contact LEFT JOIN Task ON Task.WhoId = Contact.Id

Custom Relationships

You can specify a JOIN condition that is a custom relationship. The following query retrieves the names of all Account records and the first names of all Contacts that match the specified JOIN condition:

SELECT Contact.Firstname, Account.Name 
FROM Account 
JOIN Contact 
ON Account.MyCustomColumn__c = Contact.Id

Server-Side JOIN Syntax

The cmdlet internally analyzes the Certinia objects' relationships and tries to resolve and translate as many SQL JOINs as possible into Certinia relationship queries for faster and better performance.

You can use the syntax detailed below to execute JOINs on Certinia objects that are processed by the Certinia servers.

The following query returns the first names of all the Contacts in the organization and for each Contact the name of the parent Account associated with that Contact.

SELECT Contact.Firstname, Account.Name
FROM Contact LEFT JOIN Account ON Contact.AccountId = Account.Id

Certinia supports INNER JOINs. The following query retrieves all Account records that are associated with an Opportunity:

SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDate 
FROM Account
INNER JOIN Opportunity 
ON Account.Id = Opportunity.AccountId