0

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?

2
  • The first query doesn't seem to match the result, you have Account.ProviderType__c : Private on them, are the queries and results correct?. 2nd question. Is Referal__c a lookup/md to the Case object? May 24, 2020 at 8:33
  • @SergioAlcocer Edited and changed my question. Yes Referral__c is lookup to case object. May 24, 2020 at 9:25

2 Answers 2

1

Would the following query be what you are looking for?

SELECT 
    con.Name, con.Referral__c, con.Role__c,
    case.Id, case.ProgramType__c, case.Program__c, 
    acc.ProviderType__c, acc.Sector__c
FROM Contact con, con.Referral__r case, case.Account acc 
WHERE acc.ProviderType__c = 'Public'
3
  • Its giving the output only for the cases that are having contacts. But i need all the cases eventhough if there is no contacts May 24, 2020 at 12:39
  • Since you could have 0-N Contacts per case according to your design, you won't be able to have a "flat" structure from the Case Object. The closest would be to use sub-queries, but you will end-up with 1 row per Case and as many Contacts as linked in a List inside the subquery) May 24, 2020 at 13:02
  • Some How I can able to achieve this through in apex by using two queries with the base of your answer.Thank you May 24, 2020 at 13:41
0

The columns that you wanted to add to the final output are from Case (parent) and Account (grandparent) and you already have the Referral__c lookup field to Case. So you should be able to reference them fields using dot notation:

SELECT Name, Role__c, Referral__r.Account.ProviderType__c, Referral__r.Account.Sector__c, Referral__r.ProgramType__c, Referral__r.Program__c
FROM contact

OR if your Contact's account is the same as the Case's account. Then simply get the Account field from the parent level:

SELECT Name, Role__c, Account.ProviderType__c, Account.Sector__c, Referral__r.ProgramType__c, Referral__r.Program__c
FROM contact
3
  • But I want some columns from case select id,Program__c, ProgramType__c,casenumber from Case May 24, 2020 at 9:35
  • @user3210613, your output table didn't indicate you want those columns. May 24, 2020 at 9:38
  • Sorry My bad. Because of space constraint I omitted some columns. Edited my question and saved properly with requirements May 24, 2020 at 9:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .