Salesforce Connector: SOQL JOIN Query-left join

[Shelton_Thompson]

Hello

I am trying to  join two tables using SOQL in the SalesForce connector using a left join so I only download what I need (due to the number of rows I want to query the data before pulling otherwise it is to large), but I am unsure how to write the SOQL code.

BMCServiceDesk__Incident_Service_Target__c is the parent table in which I need the following fields: Id,Name,CreatedDate,LastModifiedDate,LastActivityDate,LastViewedDate,LastReferencedDate,BMCServiceDesk__ClockPauseDate__c,BMCServiceDesk__ClockState__c,BMCServiceDesk__ElapsedTime__c,BMCServiceDesk__EndDate__c,BMCServiceDesk__Incident__c,BMCServiceDesk__IsCanceled__c,BMCServiceDesk__PausedDuration__c,BMCServiceDesk__SLAName__c,BMCServiceDesk__ServiceTargetName__c,BMCServiceDesk__Service_Target_Duration__c,BMCServiceDesk__StartDate__c,BMCServiceDesk__StateValue__c,BMCServiceDesk__Status__c,BMCServiceDesk__TargetEndDate__c,BMCServiceDesk__TargetTypeValue__c,BMCServiceDesk__TargetBusinessDays__c,BMCServiceDesk__Target_Duration__c

BMCServiceDesk__Incident__c is the child table in which I need the following fields:  Id, Name, Queue_Name_C

I would join where the field Name from both tables equals one another and I would want to do a left join so I get everything from  BMCServiceDesk__Incident_Service_Target__c  and only what matches from BMCServiceDesk__Incident__c 

I would then want to further filter my results to where Queue_Name_C from BMCServiceDesk__Incident__c contains CBS-NA

Thank you

[nick_ceneviva]

I would think so.  That syntax will only work if the Parent Child relationship is defined.  The alternative would be to use two Salesforce input connectors that only return the desired columns from the two tables.  The where clause on CBS-NA could be included to limit the number of records from the SF API. The left join could then be done within Alteryx through the use of the join tool and then using the union tool to union the L and J output to get the true Left Join,

[Shelton_Thompson]

CORRECTION
I need to join on Id from BMCServiceDesk__Incident__c  and BMCServiceDesk__FKIncident__c  from BMCServiceDesk__Incident_Service_Target__c 

[nick_ceneviva · Accepted solution]

I think there are two options to get this to work:

1. Define the relationship within Salesforce

2. Bring in the three tables that you need and do all of the joins within Alteryx.  This probably won't perform quite as well as having a query that handles the join but if you just bring back the necessary columns and apply necessary filters, the performance shouldn't be an issue.