Help with SOQL query over 3 objects

Question

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.

Answer 1

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).

Answer 2

Try this.

Select (Select Contact__c From Patient__r)
From Disease__c s 
WHERE Disease__c.Name == 'Disease X'

Note: Contact could be __c or just Contact.

Hope this helps !

Answer 3

I think you need to query from the Patient object, try this:

for (Patient__c pat : [ SELECT
                            Id,
                            Disease__c,
                            Disease__c.Name,
                            Contact__c
                        FROM
                            Patient__c
                        WHERE
                            Disease__c.Name = 'Some Disease'    
                        LIMIT
                            1000    ]) {

    targetObjectIds.add(pat.Contact__c);                            
}