Missing columns: Salesforce SOQL Multiple Join Query between two child objects (Team Members and Payment)

Question

I'm trying to do a 'Join" to calculate what a team member gets paid based on money we receive.

On my Opportunity, I have two related lists, both Master/Child 1) Team Member - with the people who worked on the opportunity and the commission they should receive. They get paid when we get paid. 2) Payments - with the payments we receive.

I need to run a join query to match every Payment we receive in a period to the related team members of the same opportunity.

This query seems to work

SELECT Amount__c, Method__c, Opportunity__c ,OwnerId, User__c, RoleCode__c,  Name FROM Sales_Team__c  where Opportunity__c IN (SELECT Opportunity__c  FROM Payment__c) LIMIT 10

However, I need to see the Payment_Amount on the Payment object so I added it to the second 'select'

SELECT Amount__c, Method__c, Opportunity__c ,OwnerId, User__c, RoleCode__c,  Name FROM Sales_Team__c  where Opportunity__c IN (SELECT Opportunity__c  FROM Payment__c) LIMIT 10

Now I get this error

There has been an error in the SOQL query:
MALFORMED_QUERY: 
Opportunity__c IN (SELECT Opportunity__c, Payment_Amount__c  FROM Payment__c)
                                       ^
ERROR at Row:1:Column:148
unexpected token: ,

How do I pull in additional columns from my Payments table?

Any and all help would really appreciated. Ian

Cyclotron3x3 Cyclotron3x3 · Accepted Answer · 2016-09-20T15:33:38

When you are looking for Opportunity__c in where clause , why are you adding Payment_Amount__c in the query.

SELECT Opportunity__c, Payment_Amount__c  FROM Payment__c

This should work fine unless there is a typo in your query

SELECT Amount__c, Method__c, Opportunity__c ,OwnerId, User__c, RoleCode__c,  Name FROM Sales_Team__c  where Opportunity__c IN (SELECT Opportunity__c  FROM Payment__c) LIMIT 10

Missing columns: Salesforce SOQL Multiple Join Query between two child objects (Team Members and Payment)

2 Answers