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]

Give this a try!

SELECT BMCServiceDesk__Incident_Service_Target__c.Id,BMCServiceDesk__Incident_Service_Target__ct.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, (SELECT BMCServiceDesk__Incident__c .Id, BMCServiceDesk__Incident__cName, Queue_Name_C From BMCServiceDesk__Incident__c Where Queue_Name_C Like '%CBS-NA%') 

From BMCServiceDesk__Incident_Service_Target__c

That will bring back everything from the BMCServiceDesk__Incident_Service_Target__c table and only information from the BMCServiceDesk__Incident__c table where the Queue name contains CBS-NA.  If you are only looking for records from both tables were the Queue Name is CBS-NA the query will look slightly different but happy to help with that!

One thing to be careful of when working with the Salesforce Connector is that sometimes it doesn't treat enters as spaces so make sure there are spaces before every line break in you query.

[Shelton_Thompson]

Almost there:

I am looking to LEFT JOIN the two tables based on the name field where the field Name from BMCServiceDesk__Incident_Service_Target__c = Name from BMCServiceDesk__Incident__c  (with BMCServiceDesk__Incident_Service_Target__c as the parent/left table) and then filter that join down to where Queue_Name_C  from BMCServiceDesk__Incident__c   contains CBS-NA

[nick_ceneviva]

Just so we are on the same page.  If a record in the BMCServiceDesk__Incident_Service_Target__c table does not have a record in the BMCServiceDesk__Incident__c table, do you want to see it in the end result?

[Shelton_Thompson]

Yes

[nick_ceneviva]

I'm thinking the query I posted should work.  What is the relationship between the two tables? one:one?

[nick_ceneviva]

Just kidding.. You called that out.  The query I posted is a manipulation of the following from Salesforce Documentation (https://developer.salesforce.com/blogs/developer-relations/2013/05/basic-soql-relationship-queries.h...)

[Shelton_Thompson]

Want to make sure I am on the same page you and fully understanding. The field in which the tables have matching values is the Name  field. Would the query you posted match based on that? 

Thank you for your help and quick response. 

[nick_ceneviva]

Yep because it is a parent-child relationship, it will know how to match the two fields.  By including the where clause on the queue field, you are limiting where there are matches

[Shelton_Thompson]

Just tried it, and got this error. I think we need to define that parent and child relationship.