SOQL help with subquery where clause

  • I have a two tables, service order and a child table called status history (1 to M relationship). I am trying to find all service order's that have a status history record matching 'Order Complete'.

    Here is my query

    SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
    FROM Service_Order__c
    WHERE id = (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')
    

    I think it is pretty self explanitory, however I recieve the following error in the debug console....

    Unknown error parsing query

    UPDATE:
    New query but still gives an error...
    SOQL

    SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
    FROM Service_Order__c
    WHERE id IN (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')
    

    Error

    WHERE id IN (SELECT id FROM Status_History__r WHERE Name = 'Order ^ ERROR at Row:3:Column:29 sObject type 'Status_History__r' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names.

  • SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
    FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')
    

    The above should work I believe and worth trying

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM