With junction object - how to merge these 2 queries?

Question

I have a junction object of Oppty and Custom-Object-license

Opportunity -- LicenseOfOpportunityJunction -- License

I am trying to get a certain field from License (license.dongle_name) from a query that is running on Opportunity.

The following all works fine:

Just getting the id from the junction

Select Id, Name, OfLicense__c FROM Opportunity_and_License__c WHERE Ofopportunity__c = 'id18chars'

Getting info form Opportunity, as well as from oppty line item - works

SELECT Id, amount, accountid , account.name,(SELECT id, Discount, listprice, name, opportunityid, Product2Id, unitprice, Quantity,totalprice FROM OpportunityLineItems) FROM Opportunity WHERE Id = 'id18chars'

Now, I want to merge both, and NOT use 2 queries.

Tried this:

SELECT Id, amount, accountid , account.name,(Select Id, Name, OfLicense__c FROM Opportunity_and_License__c),(SELECT id, Discount, listprice, name, opportunityid, Product2Id, unitprice, Quantity,totalprice FROM OpportunityLineItems) FROM Opportunity WHERE Id = 'id18chars'

I get an error:

(Select Id, Name, OfLicense__c FROM Opportunity_and_License__c),(SELECT ^ ERROR at Row:1:Column:81 Didn't understand relationship 'Opportunity_and_License__c' in

This is understood, but I don't understand how I CAN do it.

How do I merge these 2 queries into one?

Answer 1

Most probably the relationship name is not Opportunity_and_License__c.

You can verify this by clicking on the 'OfOpportunity' field label in the field list you copied in your question, and then use the 'Child Relationship Name' at the right side of the screen. Then add '__r' to that name.

Probably something like Opportunity_And_License__r