Subquery in Junction Object, can't get relationship

Question

I've got a Junction Object, Product_Dependencies_Association which has a Mast-Detail Relationship to a My_Custom_Product__c Object named Child_Product__c and a Lookup relationship (not Master-Detail) that also points to a My_Custom_Product__c Object named Parent_Product__c.

The Child_Product__c relationship's Child Relationship Name is Child_Product_Relationship.

My question is, why won't the following query work?

[select (SELECT Name FROM Child_Product_Relationship__r) FROM Product_Dependencies_Association__c]

It fails with `Error:

Compile Error: Didn't understand relationship 'Child_Product_Relationship__r' in FROM part of query call. 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.`

Interesting to note: https://workbench.developerforce.com reports the relationshipName for the Child_Product__c relationship as Child_Product__r, not Child_Product_Relationship__r as I defined within the Salesforce UI. I've also tried Child_Product__r and Child_Product_Relationship in the query and get the same error.

Details of the Child_Product field, as requested:

Answer 1

Based on your screenshot I suspect "My_Custom_Project__c" is the parent in the relationship and Product_Dependencies_Association__c is the child in the relationship.

The below query should confirm this suspicion.

[SELECT Name (SELECT Name FROM Child_Product_Relationship__r) FROM My_Custom_Project__c ]

If this is was not your intent remove the "Child_Object__c" field from the "Product_Dependencies_Association__c" object. Instead create it on the "My_Custom_Project__c" as a Master/Detail looking up to the "Product_Dependencies_Association__c" object

Answer 2

The child relationship name "Child_Product_Relationship" is the name of the relationship to the Product_Dependencies_Association__c child records from the point of view of the My_Custom_Product__c parent record.

You're trying to use it from the opposite direction in your query which is why you're receiving the error about an unknown relationship name. That name exists on the parent (master: My_Custom_Product__c) and points to the list of its children (detail: Product_Dependencies_Association__c).

[SELECT Id
    , Name
    , (SELECT Id
          , Name 
       FROM Child_Product_Relationship__r) 
 FROM My_Custom_Product__c]

This query produces a list of My_Custom_Product__c records and a list of Product_Dependencies_Association__c children under each one of them. The list of these child records is referred to by the name Child_Product_Relationship__r.