+ Start a Discussion
Doc_BDoc_B 

Multi/Nestled/Aggregated selects

Forgive me if this is the wrong forum, I have a question the abilities of SOQL:

I'm trying the following query:
Select Name,
       (select Contact.Email, Contact.LastName, Contact.FirstName, Role
               From OpportunityContactRoles
               Where ((Role = 'Investor' OR Role  = 'Investor - Primary')) ),
       (select Contact.Email, Contact.LastName, Contact.FirstName, Role
               From OpportunityContactRoles
               Where (Role = 'RR' ) )
From Opportunity Where (Name Like 'HH%')

When I run it through Apex Explorer I get the following message:
Query Failed: MALFORMED_QUERY: Cannot follow the same aggregate relationship twice: OpportunityContactRoles

What I would like to do is for each Opportunity return the names and email address of the investor and their RR.  I need all of the names and email address to be on the same row when exported out.

Is this even possible with SOQL?  

Doc B.
Ron HessRon Hess
here is a starting query, you can modify this to add the contact fields you need, this runs on my normal Dev Edition.

Select o.Role, o.Opportunity.Name, o.OpportunityId, o.Contact.Email, o.ContactId From OpportunityContactRole o where (o.Role = 'Business User' or o.Role = 'PR' or o.Role = 'User') and o.Opportunity.Name like 'Edge%'
Doc_BDoc_B
That's a nice query but it doesn't quite return the data as I want it.  The Query will return 3 row's for all matched opportunity ('edge'), a row for the contact information for User, PR, and Business User.

What I would like to do is have a query that returns 1 row that contains the User, PR, and Business User.
Ron HessRon Hess
i don't know how to do that.
Depending on the language i'd iterate over the query result and format the data into a row that meets my needs. Is that possible in the language you are running this from?