My First query is returning like this
select id,ProgramType__c,Program__c, Account.ProviderType__c,Account.Sector__C from Case where Account.ProviderType__c ='Public'
Id ProgramType__c Program__c Account.ProviderType__c Account.Sector__C
--- ------------- ----------- -------------- -------
5009D000002fEHzQAM RCC MFP Public Urban
5009D000002fEHzBAW CPA MPC Public Rural
I will take out the id from first query(case object) and will query contact object as
select Referral__c,Name,Role__C from contact where Referral__c in (select Id from case)
Referral__c Name Role__C
------------ ---- -------
5009D000002fEHzQAM James Applicant
5009D000002fEHzQAM Antony Co Applicant
5009D000002fEHzQAM Williams Co Applicant
5009D000002fEHzBAW Mark Applicant
5009D000002fEHzBAW Spencer Co Applicant
Finally i Want output like this
ID Name Role ProviderType__c Sector__C ProgramType__c Program__c
------------ ----- ----- ---------- ------- -------------- -----------
5009D000002fEHzQAM James Applicant Public Urban RCC MFP
5009D000002fEHzQAM Antony Co Applicant Public Urban RCC MFP
5009D000002fEHzQAM Williams Co Applicant Public Urban RCC MFP
5009D000002fEHzBAW Mark Applicant Public Rural CPA MPC
5009D000002fEHzBAW Spencer Co Applicant Public Rural CPA MPC
I know we have to query like this ,as account as parent object and others as child
select id,(select id from cases),(select id from contacts) from account
But I am doing other way around as case as my parent and contact as child as they designed object like that
I tried all my best to achieve this by single query and by two queries in apex by putting first into Map and second into list but I am not able to achieve.
Can you please help on this?
Account.ProviderType__c : Private
on them, are the queries and results correct?. 2nd question. Is Referal__c a lookup/md to the Case object?