6

I want to join 2 tables in a SOQL query. I looked at QueryResult but think it might be a red herring. Difficult to tell from the documentation.

I have a lookup field between table CustomObj and Event. Assuming I have a relationship called Events can I do something like this?

List<CustomObj__c> objects = [Select Id, Name, (Select Id, DataField__c From Events) 
                              From CustomObj__c];

If this works how do I access the fields from the sub query? i.e. can I do this?

objects[0].DataField__c

What happens if the field name of the out query match the field names of the inner query?

Is it easier to forget the join altogether and do 2 queries?

1
  • I was confused about the direction of the relationship. Checking the schema in Eclipse Force plugin helped to clarify. Now I'm using a child to parent syntax. ONe other tip is to use the foreign key name when dealing with child to parent. That is not the same as the child relationship name. – wisemanIV Nov 9 '13 at 7:43
11
for(CustomObj__c record: objects) {
    for(Event eventRecord: record.Events) {
        // each eventRecord has data
    }
}

You can access them individually as an array, too:

objects[0].Events[0].DataField__c

There will never be a conflict, because the child object will be in its own array.

4
  • Ok, makes sense. However when I run the SOQL in the developer console and copy paste the child relationship name from the object definition it reports 'didnt understand relationship'. I've tried with and without __r. Any ideas? – wisemanIV Nov 9 '13 at 2:23
  • I'd need to see the query to figure out what's going on. Generally speaking, though if it's a custom relationship, it uses __r, if it's a standard object (such as Event), it won't use __r. – sfdcfox Nov 9 '13 at 2:57
  • select Id, Name, (select Id, Title__c from MyNamespace__Rules__r) from MyNamespace__Rule__c there is a lookup field on object MyNamespace__Rule and in the field definition the child relationship is called Rules – wisemanIV Nov 9 '13 at 4:38
  • So I think I need to do something like this but still cant get it to work in the dev console - select Id, Name, (select Id from MyNamespace__Rule__c.Rules__r) from MyNamespace__Rule__c – wisemanIV Nov 9 '13 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.