Joining Dynamics 365 CRM object tables in Azure Synapse

Question

I'm trying to join two objects - Accounts and Deals with an SQL query in Azure. The objects are linked in Dynamics 365 (every Deal must be associated with an Account).

I don't see how I can access the Account field from the Deals table in Azure. I've checked for relationship tables, but nothing seems to allow me to join these two tables. This should be a simple query.

Both ko_account and ko_account_ko_deal_309 are in Dynamics 365, but not on the Azure tables.

Screenshot 2024-01-05 at 3.07.09 PM.png

Screenshot 2024-01-05 at 3.06.57 PM.png

Answer 1

Hi Jake Watson,

Thank you for posting query in Microsoft Q&A Platform.

It seems you are not importing the ko_account and ko_account_ko_deal_309 fields as Columns in Accountsand Deals table. You have to consider changing your import logic of these tables so that tables can import that relation ship columns as well.

Once we have that relation column with us then we can easily write SQL query on top of it using join keyword in SQL. Hope this helps. If not, kindly consider sharing more details on how you are importing tables in to Synapse SQL.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

The screenshots you've uploaded show the relationships and columns in the Dynamics 365 CRM tables, but you're noting that the corresponding fields ko_account and ko_account_ko_deal_309 are not visible in Azure Synapse.

To join the Accounts and Deals tables, you typically need a common identifier between them. In Dynamics 365 CRM, this is often a GUID field that represents the unique identifier for a record in an entity (table). In the case of Accounts and Deals, each Deal record would typically have a field that contains the Account GUID, representing the relationship between the Deal and its associated Account.

Your query might be like below :

SELECT *

FROM Deals AS D

JOIN Accounts AS A

ON D.AccountIdentifier = A.AccountIdentifier