The provider supports JOIN queries based on Dynamics CRM relationships. JOIN queries in Dynamics CRM can only be executed against related entities.

Dynamics CRM entities can be linked using relationships. The standard Dynamics CRM entities already have relationships defined for them. You can define relationships for your custom entities. The provider supports standard SQL syntax instead of proprietary FetchXML to allow easy integration with a wide variety of SQL tools.

Inner Joins

Inner joins are the default join when the JOIN keyword is specified. The INNER and NATURAL keywords are also supported. The following query returns the Names of all Accounts that have Contacts and the FirstNames of those Contacts.

SELECT Account.Id, Account.Name, Contact.FirstName, Contact.LastName FROM Account JOIN Contact ON Account.Id = Contact.AccountId_Id

Left Join

Left joins can be executed with the LEFT JOIN and LEFT OUTER JOIN keywords. The following returns all Accounts and the Equipment Id for any preferred Equipment defined for that Account:

SELECT Account.Id, Account.Name, Equipment.Id AS Eid, Equipment.Name AS Ename FROM Account LEFT JOIN Equipment ON Account.PreferredEquipmentid_id = Equipment.Id WHERE Account.Name = 'Adventure Works (sample)'