Inner Join two tables with SOQL

Question

Hello!

I am trying to create an inner join between two tables in my soql query but not sure how to go about it. Keep on getting errors with '__r'.

These two tables have different names but have similar data. I am looking to add in Family in my Product2 table to my Contact table based on the condition if ProductCode = BU_TRK_Effective_Program2__c. I don't want to use the Find Replace tool as I am looking for alternative.

SELECT
X18_character_case_safe_ID__c,Id,Collegis_Assigned__c,BU_Employer__c,BUS_RULE_DT_MAIN__c,BUS_RULE_SOURCE_MAIN__c,BU_Affiliate_Flag__c,BU_Attribution_Campus__c,CBE_Refusal_Reason__c,BU_Disposition_Category__c,BU_Disposition_Reason__c,BU_TRK_Effective_Program2__c,BU_TRK_Effective_Session2__c,Effective_Status__c,First_Business_Rule_Attempt__c,First_Business_Rule_Contact__c,First_Business_Rule_Transfer__c,BU_Latest_Application_Create_Date__c,FTCO_Session__c,BU_Latest_Application_Submit_Date__c,Most_Recent_Disposition__c,BU_Refusal_Date__c,BU_Refusal_Reason__c,BU_TRK_Application_Submit_Date__c,Valid_to_Dial__c,CBE_Inquiry_Refusal_Date__c,BU_TRK_Application_Decision_Date__c
FROM
Contact

SELECT
Family,ProductCode
FROM
Product2

Raj · Answer

@bryanmac_92 
you can try the other way around 
where you will be using 2 salesforce data tools to bring 2 different table and then join them in Alteryx.

bryanmac_92 · Answer

Hi @Raj

It says theres an unexpected token  'BU_TRK_Effective_Program2__c'

in this line: (SELECT Family FROM Product2__r WHERE ProductCode = BU_TRK_Effective_Program2__c)

Raj · Answer

@bryanmac_92 
you can try this Query

SELECT
X18_character_case_safe_ID__c,
Id,
Collegis_Assigned__c,
BU_Employer__c,
BUS_RULE_DT_MAIN__c,
BUS_RULE_SOURCE_MAIN__c,
BU_Affiliate_Flag__c,
BU_Attribution_Campus__c,
CBE_Refusal_Reason__c,
BU_Disposition_Category__c,
BU_Disposition_Reason__c,
BU_TRK_Effective_Program2__c,
BU_TRK_Effective_Session2__c,
Effective_Status__c,
First_Business_Rule_Attempt__c,
First_Business_Rule_Contact__c,
First_Business_Rule_Transfer__c,
BU_Latest_Application_Create_Date__c,
FTCO_Session__c,
BU_Latest_Application_Submit_Date__c,
Most_Recent_Disposition__c,
BU_Refusal_Date__c,
BU_Refusal_Reason__c,
BU_TRK_Application_Submit_Date__c,
Valid_to_Dial__c,
CBE_Inquiry_Refusal_Date__c,
BU_TRK_Application_Decision_Date__c,
(SELECT Family FROM Product2__r WHERE ProductCode = BU_TRK_Effective_Program2__c)
FROM
Contact

mark done if solvd.