This section discusses some of the features and restrictions that are specific to how the driver supports joins. If possible, the CData JDBC Driver for FinancialForce attempts to perform joins server side. Joins that FinancialForce cannot process are performed client side when SupportEnhancedSQL is set to true. The CData JDBC Driver for FinancialForce supports server-side joins based on Salesforce Object Query Language (SOQL). The driver supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. Join queries in FinancialForce are based on the relationships among FinancialForce objects.

Relationship Queries

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

Parent to Child Relationships

FinancialForce 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, Contact

Polymorphic Relationships

FinancialForce 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. To return only Tasks referring to a Contact on the Who relationship, specify the type of entity in the relationship:

SELECT Task.Subject, Contact.Name
FROM Task, Contact 
WHERE Contact.Type='Contact'

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

You can use the syntax detailed below to execute joins on FinancialForce objects that are processed by the FinancialForce servers. Joins on related objects are processed on the server. A benefit of predefined relationships is that you do not need to specify the join conditions to execute a join with FinancialForce data; the conditions are already accounted for based on the relationship.

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, Account

If there are multiple relationships between the tables, you can explicitly set the join criteria. The following query matches on a custom relationship determined by the columns in the join criteria (Account.MyCustomColumn__c and Contact.Id), instead of the default parent-to-child relationship between Accounts and Contacts:

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

FinancialForce 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

The following query shows a join between three custom objects. This query joins the custom tables NW_Product__c, NW_Category__c, and NW_Suppliers__c and returns the Name field from each of them. Additionally, the results from the NW_Category__c table are filtered for names that start with "Dairy":

SELECT A.Name, B.Name, C.Name 
FROM  NW_Product__c as A, NW_Category__c as B, NW_Suppliers__c as C 
WHERE B.Name LIKE 'Dairy%'