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
Wm Peck 1958Wm Peck 1958 

how to do a basic join in SOQL with WHERE clause on related table

I want to do a "left join" of CONTACT table to TargetX_SRMb__Application__c, but I want to filter the Application table by "Class_Year = '2021'

and can't seem to get that.

I can do a successful query like this but ALL rows are returned, so it's ignoring the WHERE clause in my inline (SELECT ...) 
select 

ais_candidate_number__c,
AIS_Record_Type_Name__c, 
TargetX_SRMb__Status__c,

FirstName, LastName, birthdate,
TargetX_SRMb__Gender__c,

(select 
TargetX_SRMb__Application_Decision__c,
TargetX_SRMb__Start_Term_Year__c,
AIS_Feeder_Source__c
from TargetX_SRMb__Application__r
where TargetX_SRMb__Start_Term_Year__c = '2021')

from Contact
where AIS_Record_Type_Name__c = 'Recruitment_Manager_Student'
  and TargetX_SRMb__Application__r.TargetX_SRMb__Start_Term_Year__c = '2020'
But if I flip the query I can't get it to work ... CONTACT__r doesn't work ...  
select 
name,
recordtypeid,
targetx_srmb__contact__c,
targetx_srmb__application_decision__c,
targetx_srmb__start_term_year__c,
(select 
AIS_PRMI_Id__c, 
ais_candidate_number__c,
AIS_Record_Type_Name__c, 
TargetX_SRMb__Status__c,
FirstName, LastName, birthdate,
TargetX_SRMb__Gender__c, 
usna_international__c
from Contact__r
)
from TargetX_SRMb__Application__c
where targetx_srmb__start_term_year__c = '2021'
Just learning SOQL so it's a challenge.

I'm trying to do this, logically:
(
select [this and that],
   (select [other stuff] from Table_B)
from Table_A
)
where Table_B_field = '2021'

I can do this in Oracle with a snap, but how to do so in Salesforce / SOQL / Workbench.
  
*** No idea what Topic to put this under ...

​​​​​​​

 
Best Answer chosen by Wm Peck 1958
vishal-negandhivishal-negandhi

If this object has a lookup relationship to Contact, then by correcting the query, it should work.

You've added the field after FROM in the SOQL, which isn't the right way.

Try the below query please

SELECT
name,
recordtypeid,
targetx_srmb__contact__c,
targetx_srmb__application_decision__c,
targetx_srmb__start_term_year__c,
Contact__r.ais_candidate_number__c,
FROM TargetX_SRMb__Application__c
WHERE targetx_srmb__start_term_year__c = '2021'

This should work if there's a Contact__c field existing on your TargetX_SRMb__Application__c object.

 

All Answers

vishal-negandhivishal-negandhi

Hi there, 

In your flipped query, how you'd access the Contact__r fields is like this

select name, recordtypeid, targetx_srmb__contact__c, targetx_srmb__application_decision__c, targetx_srmb__start_term_year__c, Contact__r.AIS_PRMI_Id__c, Contact__r.ais_candidate_number__c, Contact__r.xx from TargetX_SRMb__Application__c

This way you'll be able to query data from the related contact record. 

And also, your query 1 should work.. when you put a filter inside the inner query, the related records that match the filter will only be returned. 

Since we don't have joins in salesforce, what we have is relationship queries

Child to parent query 
Standard objects : ex: parent:Account, child: related Contacts

Select Id, LastName, Account.Name FROM Contact

Custom objects : ex parent : Class__c, child : related Students (Student__c)
Select Name, Class__r.Name FROM Student__c WHERE xx

Parent to child query
Standard objects : ex: parent:Account, child: related Contacts

Select Id, Name, (Select LastName FROM Contacts) FROM Account 

Custom objects : ex parent : Class__c, child : related Students (Student__c)
Select Name, (Select Name FROM Student__r) FROM Class__c 

I hope this helps.

You can find more detailed documentation here - https://developer.salesforce.com/blogs/developer-relations/2013/05/basic-soql-relationship-queries.html
Wm Peck 1958Wm Peck 1958
very good, thank you! I'll study this shortly and post my results.
Wm Peck 1958Wm Peck 1958
it didn't work ...  
select 
name,
recordtypeid,
targetx_srmb__contact__c,
targetx_srmb__application_decision__c,
targetx_srmb__start_term_year__c
from TargetX_SRMb__Application__c,

Contact__r.ais_candidate_number__c

where targetx_srmb__start_term_year__c = '2021'
here's the error message ...
SOQL error message on CONTACT reference

query works fine without the "Contact__r" reference / field.
 
vishal-negandhivishal-negandhi

If this object has a lookup relationship to Contact, then by correcting the query, it should work.

You've added the field after FROM in the SOQL, which isn't the right way.

Try the below query please

SELECT
name,
recordtypeid,
targetx_srmb__contact__c,
targetx_srmb__application_decision__c,
targetx_srmb__start_term_year__c,
Contact__r.ais_candidate_number__c,
FROM TargetX_SRMb__Application__c
WHERE targetx_srmb__start_term_year__c = '2021'

This should work if there's a Contact__c field existing on your TargetX_SRMb__Application__c object.

 

This was selected as the best answer
Wm Peck 1958Wm Peck 1958
It still says "Didn't understand relationship 'Contact__r' in field path.

This worked, which I got from a colleague* 
SELECT ID,
  TargetX_SRMb__Contact__c,
  TargetX_SRMb__Contact__r.Name,
  TargetX_SRMb__Contact__r.TargetX_SRMb__Citizenship__c,
  AIS_Candidate_Number__c,
  AIS_Candidate_Status__c,
  TargetX_SRMb__Contact__r.Account.Name,
  AIS_Feeder_Source__c,
  TargetX_SRMb__Contact__r.AIS_Nominator_Source_Account__r.AIS_Congressional_State__c
FROM TargetX_SRMb__Application__c
WHERE TargetX_SRMb__Application_Type__c = 'Online Application'
AND TargetX_SRMb__Start_Term_Year__c = '2025'
ORDER BY CreatedDate
*I needed to do my homework before contacting a colleague.

There's a lot I don't understand, such as there's no object named TargetX_SRMb__Contact__c but somehow that is used to get CONTACT info.

​​​​​​​I'm good for this question, thanks for the assist, it get me started ...