+ Start a Discussion
Bill HaackBill Haack 

SOQL Junction Query

I have a Junction object name AcctContactAff which I use to create a many-to-many relationship between Contacts and Accounts and stores (among other things) the title of the person at the account.

Being new to SOQL I am really struggling to get the following to work:

SELECT Id, Name, NPI__c,
(SELECT Title__c FROM AcctContactAff__r)
FROM Contact

When I run this I get the error message: "Didn't understand relationship 'AcctContactAff__r' in FROM part of query call."

However, I can get the query to work when I do this:

SELECT Contact__r.id, Contact__r.Name, Title__c, Name
FROM AcctContactAff__c

I absolutely need to run the query from the Parent object not the Child object.

Can anyone tell me what is wrong with the first query statement?
Best Answer chosen by Bill Haack
Alex SelwynAlex Selwyn
In the AccContactAff object open the 'Contact' relationship field. There should be child relationship name.  Make sure the name matches the child query. <Child relationship name>__r.


All Answers

Alex SelwynAlex Selwyn
In the AccContactAff object open the 'Contact' relationship field. There should be child relationship name.  Make sure the name matches the child query. <Child relationship name>__r.

This was selected as the best answer
Viviana Hernández PeñaViviana Hernández Peña

this is the complete code:

I do not want to bring all the contacts associated with the account for sending email, I want it to be sent only to the contact associated with the contact to the invoice.

                //send email 
                //subject="{!Relatedto.fw1__Payment_Receipt_Email_Subject__c}" recipientType="Contact" relatedToType="fw1__Payment__c">

                //String c = ('SELECT Id, Name, Email FROM Contact where AccountId = \''+ accId +'\' and Email != null and Name != null');
                //List<Contact> listofContacts = [SELECT Id, Name, Email FROM Contact where AccountId = :accId and Email != null and Name != null];
                List<fw1__Invoice__c> listofContacts2 =  [SELECT Id FROM fw1__Invoice__c where Email_Contacto__c != null];

                List<Contact> listofContacts =  [SELECT Email FROM Contact where AccountId = :accId and Email != null];
                String[] toRecipients = new List<String> ();

                Integer count = 0;
                for (Contact oneContact : listofContacts)
                    if (count > 0)

                Id orgWide = [SELECT Id FROM OrgWideEmailAddress limit 1].id;

                for (fw1__Payment__c payment : paymentList)
                    //String[] toRecipients = listofContacts;
                    String templateApiName = 'Standard_Payment_Receipt_v3_9';
                    ID targetObjId = listofContacts[0].Id;
                    ID whatId = payment.id;
                    ID orgWideEmailId = orgWide;
                    Boolean saveAsActivity = true;

                    sendTemplatedEmail(toRecipients, templateApiName, targetObjId, whatId, orgWideEmailId, saveAsActivity);


            } catch(DmlException e) {
                System.debug('The following exception has occurred: ' + e.getMessage());
            } catch(Exception e) {
                System.debug('An exception occurred: ' + e.getMessage() + ' Stacktrace: ' + e.getStackTraceString());

    //  -------------------------------------------------------------------------
    //  HELPER method: sendTemplatedEmail
    //  -------------------------------------------------------------------------
    public void sendTemplatedEmail(String[] toRecipients, String templateApiName, ID targetObjId, Id whatId, ID orgWideEmailId, Boolean saveAsActivity) {
        //  templateId   must be ID of an Email template
        //  targetObjId must be a Contact, User, Lead Id -- also used in merge fields of template recipient.xxxx
        //  whatId    must be an SObject that is used in the merge fields of the template relatedTo.xxxx
        //  fromId    if non null, use current user, otherwise, use this ID (most likely an org wide no reply id)
        //  bcc      not permitted when using templates

        Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();

        Id templateId;
        try { templateId = [select id, name from EmailTemplate where developername = :templateApiName].id; }
        catch(Exception e) {
            System.debug('[U-03] Unable to locate EmailTemplate using name: ' + templateApiName +
                         ' refer to Setup | Communications Templates ' + templateApiName);

        email.setSaveAsActivity(saveAsActivity); // save email as activity on the targetObjId (i.e. Contact). Note activity can't be saved on Users

        System.debug(LoggingLevel.INFO, '** entered sendTemplatedEmail, to:' + toRecipients + ' templateId:' + templateId + ' tagetObjId:' + targetObjId +
                     ' whatId:' + whatId + ' orgWideEmailId: ' + orgWideEmailId);
        try {
            Messaging.sendEmail(new Messaging.SingleEmailMessage[] { email });
        catch(EmailException e) { System.debug('[U-02] sendTemplatedEmail error. ' + e.getMessage()); }