SOQL with standard objects relationship query help

Question

I need to combine these 2 queries so that I get the Case Members and Case details in one result. There are no custom fields/objects.

Both queries run independently but when I try the subquery/relationship options, I get an error message:

query #1: select id, parentid, member.name,teamrole.name from caseteammember

query #2: select casenumber, subject, status from case

query #3: Select id, parentid, member.name,teamrole.name,(select casenumber, subject, status from cases) from caseteammember ^

ERROR at Row:1:Column:89 Didn't understand relationship 'cases' 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.member.name,teamrole.name,(select casenumber, subject, status from cases) from caseteammember

Thank you!

Answer 1

try if this SOQL might give you the result:

SELECT Id, parentId, member.Name,teamrole.Name, parent.Casenumber, parent.Subject, parent.Status FROM caseTeamMember

Answer 2

You're looking for a sub-query. To do this, put the child query as a "field" on the parent's field list. You need to use the relationship name for this to work.

SELECT CaseNumber, Subject, Status, 
  (SELECT Member.Name, TeamRole.Name 
   FROM TeamMembers)
FROM Case

(Line breaks added for legibility, you can format this however you prefer.)

Note that I opted to not include ParentId, because the Case-level detail will already tell you what case you're working with.

Your main problem is that you tried to query the parent from the child, when instead you should have queried the children from the parent.