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

If possible, the adapter attempts to perform JOINs server-side. JOINs that FinancialForce with SSO cannot process are performed client-side when SupportEnhancedSQL is set to true.

The FinancialForce with SSO Adapter supports server-side JOINs based on FinancialForce with SSO Object Query Language (SOQL). The adapter supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. JOIN queries in FinancialForce with SSO are based on the relationships among FinancialForce with SSO objects.

Relationship Queries

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

Parent to Child Relationships

FinancialForce with SSO 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

FinancialForce with SSO 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 adapter internally analyzes the FinancialForce with SSO objects' relationships and tries to resolve and translate as many SQL JOINs as possible into FinancialForce with SSO relationship queries for faster and better performance.

You can use the syntax detailed below to execute JOINs on FinancialForce with SSO objects that are processed by the FinancialForce with SSO 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

FinancialForce with SSO 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