+ Start a Discussion
Oiswarja PyneOiswarja Pyne 

Nested Queries in SOQL

I have a two custom objectsin my org... Project and Events.I have written a trigger to update Project status field based on Event Status values.Project__c is a lookup field in Events__c object.

However I am getting the below error when i am saving it.
[Error] Error: Compile Error: Didn't understand relationship 'Events__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 17 column 54

//TriggerCode

trigger Cancel_Event on Events__c (after update) {
    // Get a list of Projects
    Set<Id> ProjectIDs = new Set<Id>();
    for (Events__c event : Trigger.New) {
        ProjectIDs.add(event.Project__c);
    }

    // Get the projects, and all related events that have been Cancelled
    List<Project__c> Projects = new List<Project__c>([ SELECT Id, Project_Status__c, (SELECT Project__c , Event_Status__c FROM Project__r.Events__c)
                                                       FROM Project__c
                                                       WHERE (Id in :ProjectIDs)
                                                       AND (Event_Status__c ='Cancelled')
                                                       FOR UPDATE]);

I know the query can be simplified using two select queries but I want to use nested queries.Kindly advise.

-M@X

Best Answer chosen by Oiswarja Pyne
KaranrajKaranraj
Try the below query 
SELECT Id, Project_Status__c, (SELECT Project__c , Event_Status__c FROM Events__r where Event_Status__c = 'Cancelled' ) FROM Project__c WHERE Id in :ProjectIDs

All Answers

KaranrajKaranraj
Try the below query 
SELECT Id, Project_Status__c, (SELECT Project__c , Event_Status__c FROM Events__r where Event_Status__c = 'Cancelled' ) FROM Project__c WHERE Id in :ProjectIDs
This was selected as the best answer
Oiswarja PyneOiswarja Pyne

Thanks..It worked!!

Silly me...:D

KaranrajKaranraj
Glad to know, its works for you. Can you close this thread by marking best answer.