Get all child Share object of Parent Share which is Case?

Question

I have a Case Share and its Child Share. I need to get all Share Object of Case in SOQL how we can get that ?

SELECT CaseAccessLevel,CaseId,Id,IsDeleted,LastModifiedById,LastModifiedDate,RowCause,UserOrGroupId ,
(SELECT AccessLevel,Id,IsDeleted,LastModifiedById,LastModifiedDate,ParentId,RowCause,UserOrGroupId FROM Payment__Share where ParentId = 'XXXX')
FROM CaseShare where CaseId = 'XXXXXX'

Case is Private + Payment 1 and Payment 2 OWD private. Lookup relationship between the Payment1 and Case, also Lookup relationship between the Payment2 and Case

Even this is not working

SELECT AccessLevel,Id,IsDeleted,LastModifiedById,LastModifiedDate,ParentId,RowCause,UserOrGroupId, CaseShare.Id
FROM Payment__Share 
where ParentId.case__c = '500c000000BBoHo'

Here is the code, just help to identify root cause of the issue

CaseShare cShare = new CaseShare();
cShare.caseId = Ids;
cShare.UserOrGroupId = newCase.CreatedById;
cShare.CaseAccessLevel = 'Read';
cShare.RowCause = 'Manual';
shareList.add(cShare);

Payment__Share payShare = new Payment__Share();
payShare.ParentId = payiList[i].Id;
payShare.RowCause = 'Manual';
payShare.AccessLevel = 'Read';
payShare.UserOrGroupId = payiList[i].CreatedById;
payShareList.add(payShare);


Payment_2__Share pay2Share = new Payment_2__Share();
pay2Share.ParentId = payiList2[i].Id;
pay2Share.RowCause = 'Manual';
pay2Share.AccessLevel = 'Read';
pay2Share.UserOrGroupId = payiList2[i].CreatedById;
si2ShareList.add(pay2Share);

Answer 1

This isn't possible in a single query i.e the way your trying below, as there is no any relationship between the CaseShare and Payment__Share Object:

SELECT 
CaseAccessLevel, CaseId, Id, IsDeleted, LastModifiedById, LastModifiedDate, 
RowCause, UserOrGroupId ,
(SELECT AccessLevel, Id,IsDeleted, LastModifiedById, LastModifiedDate, 
ParentId, RowCause, UserOrGroupId FROM Payment__Share where ParentId = 
'XXXX') FROM CaseShare where CaseId = 'XXXXXX'

However, The second workaround will work fine with making few changes as below:

SELECT AccessLevel, Id, IsDeleted, LastModifiedById, LastModifiedDate, 
ParentId, RowCause, UserOrGroupId
FROM Payment__Share 
where Parent.case__c = '500c000000BBoHo'