function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
amyamyamyamyamyamy 

SOQL join 3 objects(2 look up to the same 3rd object) in one query

Can anyone help me with this soql query please?

 

I have three objects:

Contact (Lookup to Location__c object by Location__c field which holds location IDs)

Location__c (has two child lookup relationships with the other 2 objects) - the two most important fields in this objects are location ID and location name

Location_by_Zip__c (Lookup to Location__c object by Location__c field which holds location IDs) - this object is actually for mapping between zip codes and location IDs

 

My query is like this:

 

SELECT id, RecordTypeId, MailingPostalCode, AccountID, Location__c
FROM Contact
WHERE Location__r.Name = (select Location__r.Name From Location_By_Zip__c where Update_Zip__c= true)

 

I got error: unexpected token '('

 

 

When I changed the query to:

 

SELECT id, RecordTypeId, MailingPostalCode, AccountID, Location__c
FROM Contact 
WHERE Location__r.Name IN (select Location__r.Name From Location_By_Zip__c where Update_Zip__c= true)

 

I got error: The inner select field 'Location__r.Name' cannot have more than one level of relationships

 

 

I really appreciate any help!

 

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
SamReadySamReady

I would suggest breaking that (select Location__r.Name From Location_By_Zip__c where Update_Zip__c= true) query out into a separate query and then in a loop build a list of strings (ie List<String> LocationNames).

 

You could then have your where clause be: WHERE Location__r.Name IN: LocationNames

 

 

All Answers

SamReadySamReady

I would suggest breaking that (select Location__r.Name From Location_By_Zip__c where Update_Zip__c= true) query out into a separate query and then in a loop build a list of strings (ie List<String> LocationNames).

 

You could then have your where clause be: WHERE Location__r.Name IN: LocationNames

 

 

This was selected as the best answer
Rahul SharmaRahul Sharma
I don't think It could work in that way, If it works then its great.
Other option is to split the query and use a set for getting results.