[SalesForce] Help with SOQL query over 3 objects

I am having problems creating a SOQL query over my 3 objects.

Contact, Patient and Disease

Patient and Disease are in a Master Detail relationship, where Disease is the Master

Contact is a Lookup field in Patient.

So, the query that I need is:
Select Id from Contact, where patient is that contact and has disease x

I need this query to populate the list targetObjects in this code:

List <Id> targetObjectIds = new List <Id> ();
List <Contact> targetObjects = new List <Contact> ();

targetObjects = [SELECT Id FROM Contact];
for(Contact cnt : targetObjects) {
  targetObjectIds.add(cnt.Id);
}

Messaging.MassEmailMessage mail = new Messaging.MassEmailMessage();
mail.setTargetObjectIds(targetObjectIds);

Tia.

Best Answer

Since Contact is a Lookup for Patient, relevant Contact id is available at Patient object itself. So something like below should work. This is for going from child to parent
SELECT Contact__c FROM Patient__c WHERE Disease__c = 'your_val'

This is for going from parent to child
SELECT Id, Name, (SELECT Contact__c FROM Patient__r) FROM Disease__c

Apply your naming conventions where it's required.
If you are facing with can not understand the relationship error better to check the correct API name by generating the WSDL (Name > Setup > Develop > API > Generate Enterprise WSDL).

Related Topic